엑셀 TEXTSPLIT 함수, 텍스트 나누기 200% 활용법 | 10분 총정리 특강

실습 가이드

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 함께 확인하실 수 있습니다.

TEXTSPLIT 함수 기초 예제

1. 엑셀 이전버전 사용자 : TEXTSPLIT 함수는 M365 버전 사용자에게만 제공됩니다. 엑셀 2021 이전 버전을 사용하는 경우, 이전 강의에서 소개한 '텍스트 나누기 공식'을 참고하면 같은 작업을 처리할 수 있습니다.
   
   
   
   오빠두Tip : 엑셀 초보자를 위한 상황별 텍스트 나누기 총정리 영상도 함께 확인해보세요!👇
   
2. TEXTSPLIT 함수 기초 사용법 : 예제파일을 실행한 후, 1번 시트로 이동합니다. 첫 번째 예제로, 아래 그림과 같이 작성된 카테고리를 각 항목으로 분류해 보겠습니다.

   

3. D5셀을 선택한 후, 아래와 같이 TEXTSPLIT 함수를 입력하면 단계별 카테고리가 한 번에 나누어집니다.
   =TEXTSPLIT(B5,">")
   '=TEXTSPLIT(텍스트,열구분자) → 열 구분자는 문장을 가로로 구분합니다.

   

4. 여러 구분기호로 나누기 : 작성한 수식을 아래로 자동 채우기하면, 아래 그림과 같이 인용 기호(">")로 구분되지 않은 카테고리는 올바르게 나누어지지 않습니다. 이러한 경우 여러 개의 구분자를 중괄호("{}")로 묶어서 작성하면 여러 구분자로 문장을 한 번에 분리할 수 있습니다.

   

5. 다시 D5셀을 선택한 후, 아래와 같이 TEXTSPLIT 함수를 입력하고 수식을 자동 채우기하면, 여러 종류의 기호로 작성된 카테고리를 한 번에 나눌 수 있습니다.
   =TEXTSPLIT(B5,{">","-",";"})
   'B5셀에 작성된 문장을 인용기호(>), 대시(-), 세미콜론(;)으로 구분합니다.

   

6. 문장을 표로 변환하기 : TEXTSPLIT 함수의 '열 구분자'와 '행 구분자'를 함께 사용하면, 한 줄로 작성된 문장을 표 형태로 나누어 효율적으로 관리할 수 있습니다. 아래 '행/열 나누기'에 작성된 문장을 보면, 각 과목과 점수는 등호("=")로, 과목/점수 그룹은 띄어쓰기(" ")로 구분되어 있습니다.

   

7. D10셀을 선택한 후, 아래와 같이 TEXTSPLIT 함수를 입력하면 각 과목과 점수가 행과 열로 분리된 깔끔한 표가 완성됩니다.
   =TEXTSPLIT(B10,"="," ")

   

8. B10셀에 작성된 문장에 새로운 과목을 추가하면, 표가 실시간으로 업데이트되는 것을 확인할 수 있습니다.

   

9. 단어 앞/뒤 공백 제거하기 : 예제파일에서 2번 시트로 이동하면, 아래 그림과 같이 카테고리를 구분하는 기호의 앞뒤로 무작위 공백이 들어간 카테고리 목록이 있습니다. 이러한 경우 TRIM 함수를 함께 활용하면 구분 기호의 앞뒤로 들어간 공백 문제를 간단하게 해결할 수 있습니다.

   

10. 2번 시트에서 C5셀을 선택한 후, 아래와 같이 TEXTSPLIT 함수를 TRIM 함수로 묶어서 작성합니다. 이렇게 수식을 작성하면, 구분 기호의 앞뒤로 들어간 공백이 제거된 단어를 깔끔하게 출력할 수 있습니다.
    =TRIM(TEXTSPLIT(B5,">"))
    'TRIM 함수는 문장에서 불필요하게 사용된 공백을 제거합니다.

    

11. 수식을 아래로 자동 채우기하여 카테고리 구분을 마무리합니다.

    

TEXTSPLIT 함수 고급 활용 예제

1. 여러 문장을 한 번에 나누기 : TEXTSPLIT 함수처럼 '분산 범위'를 반환하는 동적 배열 함수는 일반 수식과 달리, 자동 채우기 핸들을 더블클릭해도 자동 채우기가 실행되지 않는 단점이 있습니다.

   

   오빠두Tip : 실무에서 자주 사용되는 엑셀 자동 채우기 활용법과 더욱 자세한 설명은 아래 5분 기초 영상강의를 참고하세요!
   
2. 이러한 경우, TEXTJOIN 함수와 TEXTSPLIT 함수를 함께 활용하면 여러 문장을 하나의 공식으로 편리하게 나눌 수 있습니다. 예제파일에서 3번 시트로 이동한 후, C5셀에 다음과 같이 수식을 작성합니다.
   =TEXTJOIN(";",,B5:B19)
   'B5:B19 범위의 값을 세미콜론(;)으로 구분하여 한 문장으로 합칩니다.
3. 수식을 작성하면 B5:B19에 작성된 카테고리 목록이 세미콜론(;)으로 구분된 한 문장으로 합쳐집니다. 이제 TEXTSPLIT 함수를 사용해 열 구분자는 인용 기호(">")로, 행 구분자는 세미콜론(";")으로 지정하면 표를 만들 수 있습니다. C5셀에 작성한 TEXTJOIN 함수를 다음과 같이 TEXTSPLIT 함수로 묶어서 공식을 완성합니다.
   =TEXTSPLIT(TEXTJOIN(";",,B5:B19),">",";")

   

4. TEXTJOIN+TEXTSPLIT 공식 사용 시 주의사항 : 단, TEXTJOIN 함수로 문장을 합쳐서 나눌 때는 합쳐진 문장의 글자 수가 셀에 입력 가능한 최대 글자 수(32,747자)를 초과해서는 안 됩니다. 글자 수가 32,747자를 넘기면 #VALUE 오류가 반환되므로, 이 한도를 초과하지 않는 범위 안에서 문장을 합쳐야 합니다.

   

   오빠두Tip : 엑셀 파워쿼리를 사용하면 텍스트 나누기뿐 아니라 파일 취합, 데이터 정규화 등 다양한 데이터 정제 및 가공 작업을 자동화할 수 있습니다. 파워쿼리 기초-활용 강의는 위캔두 멤버십 회원에게 제공되는 파워쿼리 2주 마스터 특강을 확인해보세요!👇
   [related_posts postid="164287"]
5. TEXTSPLIT 함수 #N/A 오류 해결 : TEXTJOIN + TEXTSPLIT 공식을 작성한 상태에서, 카테고리에 다섯 번째 항목을 추가합니다. 이번 강의에서는 첫 번째 카테고리의 새 항목으로 "자라"를 추가해 보겠습니다.

   

6. 새 항목을 추가하면, 아래 그림과 같이 다섯 번째 항목이 비어 있는 카테고리에는 #N/A 오류가 반환됩니다. 이러한 경우 IFERROR 함수를 활용하면 #N/A 오류를 간단하게 처리할 수 있습니다.

   

   오빠두Tip : IFERROR 함수 대신 IFNA 함수를 사용해도 동일한 결과를 얻을 수 있습니다!👍
7. C5셀에 작성했던 TEXTJOIN+TEXTSPLIT 함수 공식을 아래와 같이 IFERROR 함수로 묶어서 작성하면, #N/A 오류가 사라진 깔끔한 표가 완성됩니다.
   =IFERROR(TEXTSPLIT(TEXTJOIN(";",,B5:B19),">",";"),"")

   

TEXTSPLIT 함수 실전 예제

1. 여러 문장을 표로 변환하기 : 예제파일의 4번 시트로 이동합니다. 지금까지 배운 내용을 종합하여, 아래 그림처럼 "메뉴: 가격" 형식으로 작성된 여러 문장을 하나의 깔끔한 표로 변환해 보겠습니다. TEXTJOIN + TEXTSPLIT 함수 공식을 활용해 직접 표를 완성해 보세요!

   

2. 범위에 작성된 여러 문장을 슬래시("/")로 합친 후, TEXTSPLIT 함수로 콜론(":")과 슬래시("/")를 기준으로 분리합니다. D5셀에 아래 수식을 입력하면, 여러 범위로 나누어진 메뉴/가격 문장이 하나의 깔끔한 표로 완성됩니다.
   =TEXTSPLIT(TEXTJOIN("/",,B5:B11),":","/")

   

3. 숫자/문자 데이터 정제하기 : 단, 아래 그림과 같이 TEXTJOIN + TEXTSPLIT 공식으로 단순히 텍스트를 나누면 데이터가 모두 '문자' 형식으로 반환되어, 이후에 데이터를 정렬하거나 집계할 때 치명적인 문제가 발생할 수 있습니다. 엑셀은 문자 데이터는 '좌측', 숫자 데이터는 '우측'으로 정렬합니다. 현재 반환된 결과를 보면 가격이 숫자임에도 좌측으로 정렬되어 있어, 문자 형식으로 반환된 것을 확인할 수 있습니다.

   

   오빠두Tip : 엑셀의 올바른 데이터 분석을 위해 꼭 알아야 할 '데이터 형식'에 대한 자세한 설명은 아래 5분 기초 영상 강의를 참고하세요!
   
4. 이러한 경우, 수식 뒤에 1을 곱해 문자 형식의 데이터를 숫자로 강제 변환하거나 VALUE 함수를 사용합니다. D5셀에 작성된 수식을 다음과 같이 수정합니다.
   =TEXTSPLIT(TEXTJOIN("/",,B5:B11),":","/")*1
   '수식에 1을 곱해 문자 형식의 숫자 데이터를 올바른 숫자 형식으로 강제 변환합니다.
   

   

5. 공식에 1을 곱하면 문자 형식의 숫자는 올바른 숫자로 변환되지만, 숫자로 계산할 수 없는 문자가 있을 경우 #VALUE 오류가 반환됩니다. 이러한 경우 IFERROR 함수를 함께 활용하면 숫자/문자 데이터를 올바른 형식으로 한 번에 변환할 수 있습니다.
   =IFERROR(TEXTSPLIT(TEXTJOIN("/",,B5:B11),":","/")*1,TEXTSPLIT(TEXTJOIN("/",,B5:B11),":","/"))
   '1을 곱해 문자 형식의 숫자는 올바른 숫자로 변환하고, 숫자로 변환할 수 없는 문자는 그대로 표시합니다.