VBA 없이 Excel에서 문자열(셀) 분할(예: 배열 수식)

VBA 없이 Excel에서 문자열(셀) 분할(예: 배열 수식)

Excel 스프레드시트에 문자열로 구분된 데이터가 있습니다.간단히 말하자면, 다음과 같은 것을 생각해 보세요."4#8#10"임의의 수의 조각으로

이것을 배열 공식과 함께 사용하기 위해 배열로 나누는 방법이 있습니까?예를 들어, 다음과 같은 것이 있습니다.SUM(SplitStr(A1,"#"))(이것은 작동하지 않습니다 - 그냥 돌아갑니다."4".)

이것에 접근할 수 있는 좋은 방법이 있습니까?내 이상적인 솔루션은 사용자가 입력한 것처럼 어레이로 끝날 것입니다.{4,8,10}배열 수식에 사용할 수 있습니다.

내 시트의 적용에서는 구분 기호 기반 텍스트를 열에 입력하고 이를 요약할 수 없습니다.제가 시트의 주요 소비자가 아니기 때문에 가능하면 VBA를 사용하지 않는 것이 좋습니다.

고마워, 테리.

XML 기능 사용:

={SUM(FILTERXML("<t><s>" & SUBSTITUTE(A1, "#", "</s><s>") & "</s></t>", "//s"))}

Update : 새로운 TEXTSPLIT 기능을 사용합니다.

항목 4,8,10을 요약하면 다음과 같은 것을 사용할 수 있습니다.

=SUMPRODUCT(1*TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1)))=1),99)))

반환되는 배열은 텍스트 배열이므로 시작 부분의 1*은 숫자로 변환하는 한 가지 방법입니다.

이 부분:

TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1)))=1),99))

배열을 반환합니다.

{"4";"8";"10"}

그리고 앞에 1*이 있습니다.

{4;8;10}

편집 6년 후에는 어레이를 생성할 수 있는 보다 간결한 공식을 사용할 수 있습니다.

및 O365 시 »SEQUENCE함수:

=1*(TRIM(MID(SUBSTITUTE(A1,"#",REPT(" ",99)),IF(SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1)=1,1,(SEQUENCE(LEN(A1)-LEN(SUBSTITUTE(A1,"#",""))+1)-1)*99),99)))

Windows용 Excel 2010+(MAC 버전 아님)의 경우FILTERXML함수:

=FILTERXML("<t><s>" & SUBSTITUTE(A1,"#","</s><s>") & "</s></t>","//s")

로 고는 다음과 .FILTERXML함수는 값을 숫자로 추출하는 반면 첫 번째 공식은 숫자로 변환해야 하는 텍스트로 추출합니다.

론 로젠펠트의 대답과 왜 그것이 그에게 효과가 없었는지에 대한 오스틴 위스머의 질문과 관련하여, 저는 그것을 분해하고 무엇이 그것을 작동하게 하는지 알아내려고 노력하는 데 한 시간의 상당 부분을 보냈습니다(제 생각).

기능의 OFFSET 부분은 분할할 텍스트(예:

"하즈|탱크|더브"

셀의 총 문자 수를 사용하여 지정된 높이로 배열 열로 복제

LEN("Haz|Tank|Doub") = 13 characters

그리고 구분 기호가 빈 공간 " "으로 대체될 때 해당 셀의 총 문자 수를 뺀 값

LEN(SUBSTITUTE("Haz|Tank|Doub","|","")) =
LEN("HazTankDoub") = 11 characters

참조 중인 셀의 구분 기호 수는 다음과 같습니다.

LEN("Haz|Tank|Doub") - LEN(SUBSTITUTE("Haz|Tank|Doub","|","")) = 2

여기에 1 추가:

LEN("Haz|Tank|Doub")-LEN(SUBSTITUTE("Haz|Tank|Doub","|",""))**+1))** 

13 - 11 + 1 = 3

이렇게 하면 분할할 개별 텍스트 문자열의 수가 표시됩니다.결과적으로 OFFSET 함수는 셀을 3만큼(내 예에서는) 별도의 행으로 복제합니다.

"하즈|탱크|더브" "하즈|탱크|더브" "헤즈|탱크|더브"

또는

OFFSET("Haz|Tank|Doub",,,LEN("Haz|Tank|Doub")-LEN(SUBSTITUTE("Haz|Tank|Doub","|",""))+1))

OFFSET("Haz|Tank|Doub",,,3) = {"Haz|Tank|Doub";"Haz|Tank|Doub";"Haz|Tank|Doub"}

그런 다음 ROW 함수에 입력됩니다. ROW 함수는 범위 내의 각 셀에 대한 행 번호를 제공합니다. 그리고 여기서 까다로워집니다.

그래서 저의 경우, 구분 기호로 텍스트가 분할된 셀 목록에서 첫 번째 셀의 행 번호는 R2에서 시작하고, OFFSET은 아래로 3개의 행을 복제합니다. 그래서 이것을 ROW 함수에 입력하면 다음과 같이 됩니다.

ROW({"Haz|Tank|Doub";"Haz|Tank|Doub";"Haz|Tank|Doub"}) = {2;3;4}

또는

ROW({$R$2:$R$4}) = {2;3;4}

괜찮은 것 같지만, 그러면 이것은 1만큼 차감됩니다.

(ROW(OFFSET($R$2,,,LEN($R$2)-LEN(SUBSTITUTE($R$2,"|",""))+1))-1)

{2;3;4} - 1 = {1;2;3}

좋아요, 그러면 99를 곱한 것입니다.

(ROW(OFFSET($R$2,,,LEN($R$2)-LEN(SUBSTITUTE($R$2,"|",""))+1))-1) * 99

{1;2;3} * 99 = {99;297;396}

이 다음 공식은 다음에 추가합니다.

+((ROW(OFFSET($R$2,,,LEN($R$2)-LEN(SUBSTITUTE($R$2,"|",""))+1)))=1)

이것은 마지막 부분과 거의 동일하지만, 1을 빼지 않고 이러한 결과 중 하나라도 = 1이 되는지 확인합니다.이는 배열로 분할하려는 셀의 행 번호가 1이라고 가정하기 때문입니다.그래서 지금은, 제 숫자를 고수하면서, 다음과 같은 일이 일어납니다.

((ROW(OFFSET($R$2,,,LEN($R$2)-LEN(SUBSTITUTE($R$2,"|",""))+1)))=1)   
((ROW(OFFSET($R$2,,,13 - 11 + 1)))=1)
((ROW(OFFSET($R$2,,,3)))=1)
((ROW({$R$2:$R$4}))=1)
(({2;3;4})=1)
(({FALSE;FALSE;FALSE})

{99;297;396} + ({FALSE;FALSE;FALSE})
{99;297;396} + ({0;0;0})
{99;297;396}

이 값은 텍스트를 추출할 시작 번호, 즉 텍스트를 추출할 텍스트 문자열의 문자 수로 MID 기능에 입력됩니다.MID 함수가 보려는 텍스트는 분할할 셀이지만 구분 기호를 99"" 공백으로 대체하여 구분합니다.

MID(SUBSTITUTE($R$2,"|",REPT(" ",99)) = "Haz                                Tank                           Doub"

위의 각 공간 사이에 정확히 99개의 공간이 있는 것은 아닙니다. 단지 표현을 보여주고 싶었을 뿐입니다.

따라서 어레이의 3개 행 각각에 대한 시작 번호는 {99;297;396}입니다.

따라서 MID 함수는 위의 텍스트 문자열에 99자로 시작하여 첫 번째 행에 대해 99자를 추출합니다.그러면 297자로 시작하여 99자를 추출합니다.

MID(SUBSTITUTE($R$2,"|",REPT(" ",99)),{99;297;396},99)
MID("Haz                            Tank                           Doub",{99;297;396},99)

MID("Haz                            Tank                           Doub",{99},99)
MID("Haz                            **|Tank                           |**Doub",{99},99)
Tank

MID("Haz                            Tank                           Doub",{297},99)
MID("Haz                            Tank                           **|Doub               
     |**",{297},99)
Doub

MID("Haz                            Tank                           Doub",{396},99)
MID("Haz                            |Tank                          Doub",{396},99)
""

마지막에는 396자로 시작하여 99자를 넘기고 아무 것도 포함하지 않고 아무 것도 반환하지 않습니다.

제 휴대폰이 $R$1 행에서 시작하면 다음과 같이 됩니다.

ROW({$R$1:$R$3}) = {1;2;3}

(ROW(OFFSET($R$1,,,LEN($R$1)-LEN(SUBSTITUTE($R$1,"|",""))+1))-1)

{1;2;3} - 1 = {0;1;2}

(ROW(OFFSET($R$1,,,LEN($R$1)-LEN(SUBSTITUTE($R$1,"|",""))+1))-1) * 99

{0;1;2} * 99 = {0;99;198}

{0;99;198} + ((ROW(OFFSET($R$1,,,LEN($R$1)-LEN(SUBSTITUTE($R$1,"|",""))+1)))=1)   
{0;99;198} + ((ROW(OFFSET($R$1,,,13 - 11 + 1)))=1)
{0;99;198} + ((ROW(OFFSET($R$1,,,3)))=1)
{0;99;198} + ((ROW({$R$1:$R$3}))=1)
{0;99;198} + (({1;2;3})=1)
{0;99;198} + (({TRUE;FALSE;FALSE})
{0;99;198} + {1;0;0}
{1;99;198}

MID에 입력하면 1자로 올바르게 시작하여 99자의 모든 문자를 오른쪽으로 추출합니다.

MID("Haz                            Tank                           Doub",{1;99;198},99)
{Haz;Tank;Doub}

그래서 저는 코드에서 두 가지를 대체했습니다. 그러면 시트에서 시작하는 위치에 관계없이 코드가 실행됩니다.

TRIM(MID(SUBSTITUTE([@Endorsements],"|",REPT(" ",99)),
(ROW(OFFSET([@Endorsements],,,LEN([@Endorsements])-LEN(SUBSTITUTE([@Endorsements],"|",""))+1))-(ROW([@Endorsements])))*99+
((ROW(OFFSET([@Endorsements],,,LEN([@Endorsements])-LEN(SUBSTITUTE([@Endorsements],"|",""))+1)))=(ROW([@Endorsements]))),99))

[@Endorsments]는 저에게 $R$2이며, 셀은 이름이 지정된 테이블에 있으며, @는 테이블의 같은 행에 해당하지만 이름이 지정된 다른 열에 해당하므로 $R2는 대체되고 여전히 작동할 수 있다고 생각합니다.

TRIM(MID(SUBSTITUTE([@Endorsements],"|",REPT(" ",99)),
(ROW(OFFSET($R2,,,LEN(R$2)-LEN(SUBSTITUTE($R2,"|",""))+1))-(ROW($R2)))*99+
((ROW(OFFSET($R2,,,LEN($R2)-LEN(SUBSTITUTE($R2,"|",""))+1)))=(ROW($R2))),99))

기본적으로 1을 빼는 대신 분할할 셀의 행 번호를 뺍니다.큰 변화는 없어요. 그래서 론은 여전히 전적으로 신용을 얻고 있지만, 저는 그것을 막 알게 된 이후로 그것을 공유하기로 결심했습니다.

데이터가 A1에 있는 경우 B1에 다음을 입력합니다.

=TRIM(MID(SUBSTITUTE($A1,"#",REPT(" ",999)),COLUMNS($A:A)*999-998,999))

그리고 복사합니다.

위에서 론의 답변을 사용하고 있었는데, 목록이 더 큰 빈 공간을 제공하고 있었고 각 데이터에 대한 제한된 크기가 마음에 들지 않아 이러한 문제가 없는 그의 답변을 바탕으로 비슷한 공식을 만들었습니다.

=MID($A$1,IFERROR(FIND(" ",SUBSTITUTE($A$1,"#"," ",SEQUENCE(LEN($A$1)-LEN(SUBSTITUTE($A$1,"#","")),,0)))+1,1),FIND(" ",SUBSTITUTE($A$1,"#"," ",SEQUENCE(LEN($A$1)-LEN(SUBSTITUTE($A$1,"#","")),,1)))-IFERROR(FIND(" ",SUBSTITUTE($A$1,"#"," ",SEQUENCE(LEN($A$1)-LEN(SUBSTITUTE($A$1,"#","")),,0))),0)-1)

=INDEX(FILTERXML("<t><s>"&SUBSTITUTE(A1,"#","</s><s>")&"</s></t>","//s"),3,1)

그래서 당신은 배열 formula.in 없이 각 인스턴스에 대해 참조할 수 있습니다. 이것은 당신이 세 번째를 참조하는 것입니다.

다음은 입력에 적용되지 않는 @Carble 답변을 기반으로 한 접근 방식입니다.a#b#c#d되돌아오기 때문에{a;b;c}마지막 항목이 누락되었습니다.여기 이 문제를 해결하고 다음을 통해 솔루션을 단순화하려는 솔루션이 있습니다.LET함수(O365 버전 2021).동시에 출시되었습니다.SEQUENCE이전 접근 방식과 이번 접근 방식에 사용됩니다.이 접근 방식은 사용자의 Excel 버전이 지원하지 않는 경우에 유용합니다. TEXTPLIT 또는 FILTERXML(여기 다른 답변에 자세히 제공됨).

의 FILTERXML다음과 같은 제한이 있습니다.웹의 경우 Excel, Mac의 경우 Excel에서 이 기능을 사용할 수 없습니다.이 함수는 Mac용 Excel의 함수 갤러리에 나타날 수 있지만 Windows 운영 체제의 기능에 의존하므로 Mac에서는 결과를 반환하지 않습니다.

에 들어 .A2 기호여기 구분 기호서(호기▁where▁the)는del및 텍스트는 다음과 같습니다.txt는 의 됩니다.LET공식을 쉽게 유지 관리할 수 있는 기능:

=LET(txt,A1,del, ";",length, LEN(txt),
 itemsNum, length-LEN(SUBSTITUTE(txt,del,""))+1,
 seq, SEQUENCE(itemsNum,,0), findSpaces, FIND(" ",SUBSTITUTE(txt,del," ",seq)),
 startTxt, IFERROR(findSpaces+1,1),startDel1, IFERROR(findSpaces,0),
 startDel2, IFERROR(FIND(" ",SUBSTITUTE(txt,del," ",seq+1)), length+1),
MID(txt,startTxt,startDel2-startDel1-1)
)

다음은 몇 가지 입력 값에 대한 출력입니다.보시다시피 빈 문자열의 경우에도 다양한 시나리오에서 작동하며 결과도 빈 문자열입니다.

설명.

주요 아이디어는 다음과 같습니다.MID(text, start_num, num_chars)그래서 모든 중간 계산은 다음을 통해 명명됩니다.LET은 다기다음식위것한입니기별하을능은▁are다것▁function입니▁identify▁to를 식별하기 위한 입니다.start_num그리고.num_chars열 배열로 표시되므로 해당 분할 배열을 반환합니다.

다음에서 추가 이름을 지정합니다.LET몇 가지 계산을 다시 사용합니다.또한 이 섹션의 중간 결과를 설명하는 데 도움이 됩니다.

itemsNum, length-LEN(SUBSTITUTE(txt,del,""))+1

입니다.del의 플러스1), 위치length다음과 같이 정의됩니다.LEN(txt).

계산의 설명 목적을 위해 우리는 참조로 사용할 것입니다.txt:a;bb;ccc;dddd다음은 해당하는 출력입니다.

길이	항목Num
13	4

seq하는 이은해니다합당입니다.4x1 순서: 배열순서:{0;1;2;3}.

중요:이 솔루션이 작동하려면 이전 시퀀스가 수평 배열이 아닌 수직 배열이어야 합니다.1x4 합니다(). 여기서 사용되는 다른 기능을 보장합니다(SUBSTITUTE,FIND,MID 수직 배열도 반환합니다.문제는 어레이 솔루션을 찾는 것이므로 이 점을 염두에 두는 것이 중요합니다.

의 결과SUBSTITUTE(text, old_text, new_text, [instance_num])양쪽에 모두 사용되는FIND 전화 번호:

SUBSTITUTE(txt,del," ",seq))	SUBSTITUTE(txt,del," ",seq+1)
#VALUE!	a bb;ccc;dddd
a bb;ccc;dddd	a;bb ccc;dddd
a;bb ccc;dddd	a;bb;ccc dddd
a;bb;ccc dddd	a;bb;ccc;dddd)(으)로 표시)

합니다. 의 각 instance_num(네 번째 입력 인수) 양수여야 합니다.그러므로 다음을 위하여0 SUBSTITUTE아온다를 합니다.#VALUE!우리는 이 사건을 나중에 처리합니다.

의 당 출 력의 해당 FIND이전 입력 인수에 대한 호출은 다음과 같습니다.

findSpaces, FIND(" ",SUBSTITUTE(txt,del," ",seq))
startDel2, IFERROR(FIND(" ",SUBSTITUTE(txt,del," ",seq+1)), length+1)

findSpaces	startDel2
#VALUE!	2
2	5
5	19
9	14

위해서startDel2,IFERROR를 대체합니다.#VALUE! 행문자열에 : " " " " (" " " " " " " " " " " " " " " " " " " " "a;bb;ccc;dddd)와 함께length+1(=14 하면 . 가 입력이 되면 )의 인수입니다. 왜냐하면 입력 인수가num_charsMID문자열의 끝에 도달하거나 문자열의 끝까지 문자 수만큼 사용합니다.

변수는 다음과 같습니다.startTxt,startDel1를 기준으로 계산됩니다.findSpaces 키워드:

startTxt, IFERROR(findSpaces+1,1)
startDel1, IFERROR(findSpaces,0)

는 다음과 같은 출력을 생성합니다.

startTxt	startDel1
1	0
3	2
6	5
10	9

startTxt구분 기호 뒤에 있는 각 하위 문자열의 시작 위치를 나타냅니다. 번째 행에 FIND아온다를 합니다.#VALUE!하지만 그것은 다음을 통해 대체되었습니다.IFERROR와 함께1문자열의 시작을 나타냅니다(txt), 두 인수()로 사용할 수 두 번 입 인 사 수 있 할 니 다 습 용 로 수start_numMID.

startDel1을 합니다.#VALUE!경유로IFERROR와 함께0 번째

startDel2-startDel1-1각 문자열의 . " 하 문 자 나 수 니 냅 타 다 를 문 각 자 위

startDel2-startDel1-1
1
2
3
4

그래서 이제 우리는 그것을 호출하는 데 필요한 모든 요소를 가지고 있습니다.MID함수:

MID(txt,startTxt,startDel2-startDel1-1)

누군가 구글 시트를 사용하여 동일한 작업을 수행하려는 경우를 대비하여 다음과 같이 수행합니다.

A1에 다음이 포함되어 있다고 가정합니다.4#8#10

=ARRAYFORMULA(SPLIT(A1, "#"))

그러면 배열이 생성되고 배열 항목이 열로 표시됩니다.

배열을 행으로 표시해야 하는 경우 다음을 사용할 수 있습니다.

=ARRAYFORMULA(TRANSPOSE(SPLIT(A1, "#")))

MS Excel에 SPLIT 수식이 있으면 좋겠습니다.

언급URL : https://stackoverflow.com/questions/25316094/split-a-string-cell-in-excel-without-vba-e-g-for-array-formula