강좌 > 엑셀중급강좌 > 엑셀 데이터 다루기 > 수식으로 한 번에 텍스트 나누기

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

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

수식으로 텍스트 나누기

실습용 엑셀파일 다운로드 :  데이터처리-수식으로-텍스트나누기.xlsx

 

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

TRANSPOSE, FILTERXML 함수로 텍스트 나누기

=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")

FILTERXML 함수로 텍스트 세로 범위에 가져오기

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"))

TRANSPOSE, FILTERXML 함수로 텍스트 나누기 - 구분자를 쉼표로 사용

 

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

'서울특별시 종로구 서린동 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"))

 


관련 글

TEXTSPLIT 함수 사용법 TEXTSPLIT 함수 – 기호로 구분하여 텍스트 나누기 - 엑셀 TEXTSPLIT 함수는 기호로 구분하여 텍스트를 열 또는 행단위로 나누어 줍니다. 이 함수는 2023년 9월 현재 Microsoft 365 이상 버전에서만…

TEXTSPLIT 함수 – 기호로 구분하여 텍스트 나누기 더 보기 »

엑셀 텍스트 나누기의 모든 것 - 엑셀을 제대로, 쉽게 사용하려면 데이터가 먼저 정리되어 있어야 합니다. 데이터가 정리되지 않은 상태에서는 수식을 복잡하게 꼬거나 난해한 방법으로 문제를 해결해야…

엑셀 텍스트 나누기의 모든 것 더 보기 »

 

엑셀웍스 책 출간 안내

그동안 엑셀웍스에 올린 '엑셀 강좌', '함수 사용법', 여러 '실무 예제'에서 핵심만 뽑아 '된다! 엑셀 수식 & 함수' 책으로 출간하였습니다.

엑셀웍스 책 출간안내  자세히 보기

온라인 구매처: 예스24   교보문고   알라딘 

댓글 남기기

Scroll to Top