수식으로 한 번에 텍스트 나누기

다음과 같이 A열에 슬래쉬, 쉼표, 공백 등으로 구분된 텍스트가 입력되어 있을 때 여러 열에 나누어서 값을 가져오려면 텍스트 나누기로 해도 되지만 TRANSPOSE, FILTERXML 함수를 사용한 수식으로 간단히 해결할 수 있습니다.

 

나눌 텍스트가 [A1] 셀에  입력되어 있을 때 [B1] 셀에 다음 수식을 입력하면 한 번에 텍스트를 나누어서 가져옵니다.

=TRANSPOSE(FILTERXML("<list><item>" &SUBSTITUTE(A1,"/","</item><item>") & "</item></list>","//item"))

TRANSPOSE, FILTERXML 함수는 범위에 값을 출력하므로 일반적인 수식 입력과 달리 배열 수식으로 입력해야 합니다. 값을 가져올 [B1:F1] 범위를 선택한 후 수식을 입력하고 [Ctrl+Shift+Enter]를 누릅니다.

* Microsoft 365, 엑셀 2021 이상 버전에서는 동적 배열 수식이 지원되므로 [B1] 셀만 선택하고 수식 입력 후 [Enter] 만 누르면 됩니다.

 

수식 풀이

수식이 복잡하므로 단계를 나누어서 풀이해 보겠습니다.

 

SUBSTITUTE 함수로 XML 텍스트 만들기

수식에서 XML 텍스트를 만드는 부분을 실행해 보면

=TRANSPOSE(FILTERXML("<list><item>" &SUBSTITUTE(A1,"/","</item><item>") & "</item></list>","//item"))

="<list><item>" &SUBSTITUTE(A1,"/","</item><item>") & "</item></list>"

SUBSTITUTE 함수로 '맥주/소주/양주/막걸리/위스키' 텍스트에서  슬래쉬(/) 부분을 '</item><item>'으로 바꾸고 앞뒤에 XML 태그를 붙이면 다음과 같은 결과가 나옵니다. XML 형식으로 만들어진 텍스트입니다.

<list><item>맥주</item><item>소주</item><item>양주</item><item>막걸리</item><item>위스키</item></list>

 

FILTERXML 함수로 텍스트 나누기

다음으로 FILTERXML 함수를 사용하여 위에서 만들어진 XML 텍스트에서 item이라는 태그로 둘싸여진 부분을 가져옵니다.

=FILTERXML("<list><item>" &SUBSTITUTE(A1,"/","</item><item>") & "</item></list>","//item")

XML item 태그에 해당하는 부분을 세로 범위에 가져왔습니다.

 

TRANSPOSE, FILTERXML 함수를 조합하여 텍스트 나누기

결과를 가로 범위에 가져와야 하므로 다음과 같이 행과 열을 바꾸어 주는 TRANSPOSE 함수를 사용합니다.

=TRANSPOSE(FILTERXML("<list><item>" &SUBSTITUTE(A1,"/","</item><item>") & "</item></list>","//item"))

 

위의 예에서는 항목을 구분하는 값이 슬래쉬(/)였지만 다음과 같이 구분값이 쉼표(,)라면 SUBSTITUTE 함수의 두 번째 인수에 쉼표를 넣어주면 됩니다.

'맥주,소주,양주,막걸리,위스키'

=TRANSPOSE(FILTERXML("<list><item>" &SUBSTITUTE(A2,",","</item><item>") & "</item></list>","//item"))

 

공백으로 구분되는 주소라면 구분값을 공백으로 입력하면 됩니다.

'서울특별시 종로구 서린동 154-1'

=TRANSPOSE(FILTERXML("<list><item>" &SUBSTITUTE(A3," ","</item><item>") & "</item></list>","//item"))

 

참고 : XLM 텍스트를 만들 때 사용한 태그명 list, item은 다음과 같이 다른 이름으로 바꾸어도 됩니다.

=TRANSPOSE(FILTERXML("<a><b>" &SUBSTITUTE(A7,"/","</b><b>") & "</b></a>","//b"))

 

---

관련 글