엑셀 텍스트 데이터 나누기 TEXTSPLIT 함수 수식

엑셀에서 대량의 텍스트 데이터를 빠르고 효율적으로 분리하고 싶을 때, TEXTSPLIT 함수는 최고의 솔루션입니다.

이 글에서는 TEXTSPLIT 함수의 기본 사용법, 대체 방법, 장단점, 그리고 실무에 바로 적용할 수 있는 고급 예제까지 자세히 소개합니다.

 

TEXTSPLIT 함수란?

TEXTSPLIT 함수는 엑셀 365 및 최신 버전에서 제공되는 동적 배열 함수로, 지정한 구분 기호를 기준으로 텍스트를 자동으로 여러 셀에 분할해주는 기능입니다.
기존에는 LEFT, MID, FIND 등 여러 함수를 조합해야 했던 작업을 단일 함수로 간단히 해결할 수 있습니다.

기본 구문

=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])

• text: 분할할 텍스트(필수)
• col_delimiter: 열 기준 구분자(필수)
• row_delimiter: 행 기준 구분자(선택)
• ignore_empty: 빈 값 무시 여부(선택)
• match_mode: 대소문자 구분(선택)
• pad_with: 빈 셀 채울 값(선택)

TEXTSPLIT Test

 

TEXTSPLIT 함수의 장점

• 여러 구분자 지원: 쉼표, 하이픈 등 다양한 구분자를 배열로 지정해 한 번에 분리 가능
• 동적 배열 반환: 결과가 자동으로 인접 셀에 확장되어 별도의 복사/붙여넣기 불필요
• 열/행 동시 분리: 열과 행 구분자를 동시에 지정해 표 형태로 데이터 분할 가능
• 복잡한 함수 조합 불필요: LEFT, MID, FIND, SUBSTITUTE 등 복잡한 함수 조합 없이 간단하게 사용
• 실시간 업데이트: 원본 데이터가 변경되면 결과도 자동 갱신

 

TEXTSPLIT 함수의 단점

• 버전 제한: Excel 365 및 최신 버전에서만 사용 가능. Excel 2019 이하에서는 지원하지 않음
• 배열 결과 충돌: 결과가 확장될 셀에 기존 데이터가 있으면 #SPILL! 오류 발생
• 문자 수 제한: TEXTJOIN 등과 조합 시 셀 최대 문자 수(32,767자) 초과 시 #VALUE! 오류
• 구분자 미존재 시 오류: 지정한 구분자가 텍스트에 없으면 #N/A 오류

 

TEXTSPLIT 대신 사용할 수 있는 방법

1. 함수 조합

Excel 2019 이하 버전이나 TEXTSPLIT을 쓸 수 없는 환경에서는 다음과 같은 함수 조합으로 대체할 수 있습니다.

=TRIM(MID(SUBSTITUTE(","&셀,",",REPT(" ",LEN(셀)+1)),ROW(INDIRECT("A1:A"&LEN(셀)-LEN(SUBSTITUTE(셀,",",""))+1))*LEN(셀)+1,LEN(셀)+1))

• SUBSTITUTE, MID, ROW, INDIRECT, TRIM 함수를 조합해 특정 구분자로 텍스트를 분리
• 여러 구분자나 줄바꿈 기준 등 다양한 변형 공식 존재

2. 파워쿼리 활용

• 파워쿼리를 이용하면 텍스트 나누기, 데이터 정규화 등 반복 업무를 자동화할 수 있음

 

 

고급 실무 예제

1. 여러 구분자로 제품 정보 분리

예시 데이터: 제품A-대-10개
공식: =TEXTSPLIT("제품A-대-10개", {"-", "개"})
결과:

제품A

대

10

2. 표 형태로 카테고리 분리

예시 데이터: 전자제품>노트북>게이밍;의류>남성>셔츠
공식: =TEXTSPLIT("전자제품>노트북>게이밍;의류>남성>셔츠", ">", ";")
결과:

전자제품	노트북	게이밍
의류	남성	셔츠

3. TEXTJOIN과 연계한 대량 데이터 처리

공식: =TEXTSPLIT(TEXTJOIN(";",,B5:B19), ">", ";")
여러 셀의 데이터를 세미콜론으로 합친 후, ">"와 ";"로 분리할 수 있습니다.

 

4. 공백 처리 및 데이터 정제

공식: =TRIM(TEXTSPLIT(A1, ","))
TEXTSPLIT으로 분리한 후, TRIM 함수를 결합해 불필요한 공백을 제거할 수 있습니다.

 

마무리

TEXTSPLIT 함수는 복잡한 텍스트 분리 작업을 혁신적으로 단순화해주는 엑셀 최신 함수입니다.
여러 구분자, 표 형태 분리, 대량 데이터 처리 등 실무에서 강력한 효율을 발휘합니다.
최신 엑셀을 사용한다면 반드시 익혀두세요!
구버전 사용자는 SUBSTITUTE, MID, TRIM 등 함수 조합이나 파워쿼리로 대체할 수 있습니다.
실제 업무에 바로 적용해 데이터 정리 시간을 획기적으로 줄여보세요!

함수 공식과 예제는 엑셀 버전 및 데이터 구조에 따라 다를 수 있으니, 실무 환경에 맞게 응용해보시길 바랍니다.