엑셀 TEXTSPLIT 함수로 쉼표·공백·줄바꿈 데이터를 나누는 법
거래처가 보낸 CSV 내용을 한 셀에 붙여넣었거나 주소·품목·태그가 쉼표와 줄바꿈으로 섞여 있을 때 값을 손으로 나누면 빠뜨리거나 잘못 옮기기 쉽습니다. TEXTSPLIT 함수는 지정한 구분 기호를 기준으로 한 셀의 텍스트를 여러 열 또는 여러 행에 동적으로 펼칩니다. Microsoft 공식 문서는 TEXTSPLIT을 텍스트 나누기 기능과 비슷하지만 수식 형태로 작동하며 TEXTJOIN의 반대 역할을 하는 함수라고 설명합니다.
중요한 점은 쉼표 하나를 넣는 데서 끝나지 않습니다. 열로 나눌지 행으로 나눌지, 연속된 구분 기호 사이의 빈값을 보존할지, 여러 종류의 구분자를 동시에 인정할지, 결과 배열의 폭이 다를 때 무엇으로 채울지를 정해야 합니다. 이 글은 주문번호·품목·옵션 데이터와 주소 데이터를 이용해 수식을 단계별로 설계하고 검증하는 방법을 다룹니다.
TEXTSPLIT 기본 구문과 인수
기본 구문은 =TEXTSPLIT(text,col_delimiter,[row_delimiter],[ignore_empty],[match_mode],[pad_with])입니다. text는 나눌 원본이고 col_delimiter는 결과를 옆 열로 펼칠 기준입니다. row_delimiter는 아래 행으로 펼칠 기준입니다. ignore_empty는 연속 구분자 사이의 빈 항목을 무시할지 결정하고, match_mode는 영문 구분자의 대소문자 구분 여부, pad_with는 행마다 항목 수가 달라 생기는 빈칸을 무엇으로 채울지 지정합니다.
| 인수 | 역할 | 실무 점검 사항 |
|---|---|---|
| text | 분할할 원본 텍스트 또는 셀 | 직접 입력보다 원본 셀 참조 권장 |
| col_delimiter | 결과를 열 방향으로 나눌 기준 | 쉼표 뒤 공백까지 포함할지 확인 |
| row_delimiter | 결과를 행 방향으로 나눌 기준 | 줄바꿈은 CHAR(10) 사용 가능 |
| ignore_empty | 연속 구분자 사이 빈 항목 무시 | FALSE가 기본이며 빈 셀을 보존 |
| match_mode | 영문 구분자의 대소문자 처리 | 0은 구분, 1은 구분하지 않음 |
| pad_with | 부족한 배열 칸의 채움값 | 기본은 #N/A, 필요하면 빈 문자열 |
Microsoft 공식 문서 기준으로 ignore_empty 기본값은 FALSE입니다. 즉 A,,C처럼 쉼표가 연속되면 가운데 빈 항목도 결과 한 칸으로 남습니다. 빈 필드가 실제 의미를 가지는 주문·설문 데이터라면 이 기본값을 유지하는 편이 안전합니다.
실전 예시 1: 쉼표로 품목 정보를 열로 나누기
A2 셀에 P-104,무선마우스,2,검정이 들어 있다고 가정합니다. =TEXTSPLIT(A2,",")를 B2에 입력하면 B2:E2에 제품코드, 품명, 수량, 색상이 각각 나타납니다. 결과는 하나의 동적 배열이므로 C2:E2에 값을 직접 입력하거나 일부만 지울 수 없습니다. 전체 결과를 바꾸려면 B2의 원본 수식이나 A2의 텍스트를 수정해야 합니다.
쉼표 뒤에 공백이 들어 있는 P-104, 무선마우스, 2, 검정 형식이라면 구분자를 ", "로 지정할 수 있습니다. 그러나 일부 행은 쉼표만, 일부 행은 쉼표와 공백을 쓴다면 일관되게 나뉘지 않습니다. 먼저 공백을 정규화하거나 여러 구분자를 배열로 지정해야 합니다.
분할한 수량은 겉보기에는 숫자여도 텍스트일 수 있습니다. 합계가 계산되지 않으면 VALUE로 변환하거나 셀 형식을 점검합니다. TEXTSPLIT은 분할 위치를 정하는 함수이지 데이터 형식을 자동 확정하는 함수는 아닙니다.
열 구분 기호와 행 구분 기호 함께 쓰기
A2에 사과,3;배,5;포도,2가 있다면 쉼표는 열, 세미콜론은 행 구분자로 사용할 수 있습니다. 수식은 =TEXTSPLIT(A2,",",";")입니다. 결과는 품목과 수량의 두 열, 세 품목의 세 행으로 이루어진 3×2 배열입니다. 이 방식은 간단한 메모를 표로 바꿀 때 유용합니다.
| 원본 조각 | 첫 번째 결과 열 | 두 번째 결과 열 |
|---|---|---|
사과,3 | 사과 | 3 |
배,5 | 배 | 5 |
포도,2 | 포도 | 2 |
구분자 선택 전에 원본 값 안에 같은 문자가 들어 있는지 확인해야 합니다. 상품명이 서울, 경기 지도처럼 쉼표를 실제 내용으로 포함한다면 열이 하나 더 생깁니다. 따옴표로 감싼 CSV의 복잡한 규칙까지 TEXTSPLIT 하나로 해석하려 하지 말고 Power Query의 CSV 가져오기나 전용 파서를 사용하는 편이 안전합니다.
줄바꿈 데이터를 행으로 바꾸는 방법
한 셀 안에 여러 항목이 Alt+Enter로 줄바꿈되어 있다면 줄바꿈 문자는 보통 CHAR(10)입니다. A2의 각 줄을 아래 행으로 펼치려면 열 구분 기호를 비워 두고 =TEXTSPLIT(A2,,CHAR(10))을 사용합니다. 수식에서 두 번째 인수 자리가 비어 있기 때문에 쉼표가 두 번 연속으로 보입니다.
줄마다 품목:수량 형식이라면 =TEXTSPLIT(A2,":",CHAR(10))처럼 콜론을 열 구분자, 줄바꿈을 행 구분자로 함께 쓸 수 있습니다. 웹 페이지나 다른 운영체제에서 복사한 텍스트에는 CHAR(13)과 CHAR(10)이 섞일 수 있습니다. 결과 끝에 보이지 않는 문자가 남으면 SUBSTITUTE(A2,CHAR(13),"")로 캐리지리턴을 제거한 값을 보조 열에서 시험합니다.
여러 구분자를 동시에 처리하기
실무 메모는 쉼표, 세미콜론, 슬래시가 뒤섞이는 경우가 많습니다. Microsoft 공식 문서는 구분 기호가 둘 이상이면 배열 상수를 사용하라고 안내합니다. 쉼표와 세미콜론 중 어느 것이든 열 구분자로 인정하려면 =TEXTSPLIT(A2,{",",";"})처럼 작성합니다.
쉼표와 쉼표+공백을 동시에 배열에 넣는 방법은 겹치는 패턴 때문에 예상과 다른 빈값을 만들 수 있습니다. SUBSTITUTE(A2,", ",",")로 먼저 하나의 형식으로 통일한 뒤 쉼표 하나로 분할하는 편이 결과를 설명하기 쉽습니다.
하이픈을 구분자로 쓸 때 날짜 2026-08-07, 음수 -15, 제품코드 AB-102까지 모두 나뉠 수 있습니다. 구분자는 눈에 많이 보이는 문자가 아니라 필드 경계를 유일하게 나타내는 문자를 선택해야 합니다. 규칙이 불명확하면 분할 전 원본 작성 규칙부터 정리합니다.
연속 구분자와 빈 항목 보존하기
원본이 A,,C일 때 가운데 빈값이 자료 없음을 뜻한다면 =TEXTSPLIT(A2,",",,FALSE)로 보존합니다. 결과는 A, 빈 셀, C의 세 칸입니다. 반대로 쉼표가 실수로 두 번 입력됐고 빈 항목에 의미가 없다면 =TEXTSPLIT(A2,",",,TRUE)를 사용합니다.
| 원본 | ignore_empty=FALSE | ignore_empty=TRUE | 권장 상황 |
|---|---|---|---|
A,,C | A / 빈칸 / C | A / C | 빈 필드가 열 위치를 뜻하면 FALSE |
A, ,C | 공백 문자열이 남음 | 공백은 빈값과 다름 | 분할 전 공백 정규화 |
A,,, | 끝 빈 항목 보존 | A만 남을 수 있음 | 설문·내보내기 규칙 확인 |
빈 문자열과 공백 한 칸은 다릅니다. ignore_empty=TRUE가 공백 문자를 자동으로 지워 주는 것으로 생각하면 안 됩니다. TRIM이나 SUBSTITUTE로 불필요한 공백을 정리하되 주소나 상품명 안의 의미 있는 공백까지 제거하지 않도록 테스트합니다.
pad_with로 모양이 다른 행 정리하기
행마다 열 개수가 다르면 TEXTSPLIT은 결과를 직사각형 배열로 만들기 위해 부족한 칸을 채웁니다. 기본 채움값은 #N/A입니다. 첫 행은 세 항목, 둘째 행은 두 항목이면 둘째 행 마지막 칸에 #N/A가 나타날 수 있습니다.
표시를 깔끔하게 하려면 =TEXTSPLIT(A2,",",";",FALSE,0,"")처럼 여섯 번째 인수에 빈 문자열을 지정합니다. 다만 #N/A를 숨기면 원본 행마다 필드 수가 다르다는 품질 문제를 놓칠 수 있습니다. 데이터를 정제하는 단계에서는 기본 오류를 유지해 비정상 행을 찾고, 보고서 출력 단계에서만 빈 문자열로 바꾸는 방식을 권합니다.
#SPILL!과 #N/A 오류 해결 순서
#SPILL!은 결과가 펼쳐질 범위에 기존 값, 병합 셀 또는 다른 배열이 있을 때 발생합니다. 수식 셀을 선택하면 Excel이 방해 범위를 표시할 수 있습니다. 결과가 몇 열·몇 행으로 늘어날지 예상하고 주변 셀을 비운 뒤 병합 셀을 해제합니다.
#N/A는 행마다 항목 수가 달라 기본 패딩이 적용됐거나 다른 텍스트 함수와 조합했을 때 구분자를 찾지 못해 발생할 수 있습니다. 먼저 TEXTSPLIT만 단독으로 계산해 어느 단계에서 오류가 생기는지 분리합니다.
| 증상 | 가능 원인 | 해결 순서 |
|---|---|---|
#SPILL! | 펼침 영역에 값 또는 병합 셀 | 결과 범위 확인 → 기존 값 이동 → 병합 해제 |
| 예상보다 열이 많음 | 값 내부에 구분자가 포함됨 | 원본 규칙 확인 → 더 고유한 구분자 선택 |
| 공백이 결과 앞에 남음 | 쉼표와 공백 조합이 불규칙 | SUBSTITUTE로 표준화 후 분할 |
#N/A가 배열 끝에 표시 | 행별 항목 수가 다름 | 비정상 행 확인 후 pad_with 결정 |
| 수식 이름 오류 | 사용 버전이 미지원 | 텍스트 나누기, Power Query 대안 사용 |
TEXTBEFORE·TEXTAFTER와 선택 기준
구분자를 기준으로 전체 항목을 여러 셀에 펼치려면 TEXTSPLIT이 적합합니다. 첫 번째 구분자 앞부분 하나만 필요하면 TEXTBEFORE, 뒤쪽 하나만 필요하면 TEXTAFTER가 더 간단합니다. 이메일 name@example.com에서 아이디만 가져오려면 =TEXTBEFORE(A2,"@"), 도메인만 가져오려면 =TEXTAFTER(A2,"@")를 사용합니다.
TEXTBEFORE와 TEXTAFTER는 몇 번째 구분자를 기준으로 할지 정할 수 있어 파일 확장자나 마지막 경로 조각처럼 끝에서 찾을 때 유용합니다. 반면 모든 태그나 품목을 한 번에 나눌 때는 TEXTSPLIT이 자연스럽습니다. 함수 선택은 어느 함수가 최신인가보다 결과가 한 조각인지 전체 배열인지에 따라 결정합니다.
구분자가 없는 행에서 #N/A가 발생하면 각 함수의 if_not_found 인수나 IFERROR를 검토할 수 있습니다. 그러나 오류를 빈칸으로 바꾸기 전에 정말 구분자가 없어도 정상인 데이터인지 확인해야 합니다.
텍스트 나누기·빠른 채우기·Power Query 비교
TEXTSPLIT은 원본이 바뀌면 결과가 자동으로 다시 계산됩니다. 반면 데이터 메뉴의 텍스트 나누기는 실행 시점의 값을 직접 여러 열에 배치하므로 원본 변경이 자동 반영되지 않습니다. 결과 위치의 기존 데이터를 덮어쓸 수 있어 복사본에서 작업해야 합니다.
빠른 채우기는 예시를 보고 패턴을 추정하므로 일정하지 않은 이름이나 주소를 나눌 때 편리할 수 있습니다. 하지만 추정 규칙이 명시적인 수식으로 남지 않아 대량 반복 작업의 검증이 어렵습니다. Power Query는 여러 파일을 반복해서 가져오고 복잡한 CSV·구분자·형식 변환을 단계로 기록할 때 적합합니다.
| 방법 | 자동 갱신 | 규칙 확인 | 적합한 상황 |
|---|---|---|---|
| TEXTSPLIT | 수식 재계산 | 셀 수식으로 확인 | 한 통합문서의 규칙적인 구분 데이터 |
| 텍스트 나누기 | 실행 시점만 반영 | 마법사 설정 | 일회성 단순 분할 |
| 빠른 채우기 | 다시 실행 필요 | 추정 규칙이 불명확할 수 있음 | 사람 이름 등 예시 기반 패턴 |
| Power Query | 새로 고침 | 적용 단계로 기록 | 반복 파일, 복잡한 정제, 대용량 데이터 |
버전 호환성과 구버전 대안
Microsoft 공식 적용 대상에는 Microsoft 365용 Excel, 웹용 Excel, Excel 2024 등이 표시됩니다. Excel 2016·2019처럼 TEXTSPLIT을 지원하지 않는 버전과 공동 작업한다면 수식이 계산되지 않을 수 있습니다. 배포 전 상대방 버전을 확인하고 결과값 전달인지 수식 편집까지 필요한지 정합니다.
구버전의 일회성 작업은 텍스트 나누기 마법사를 사용할 수 있습니다. 반복 작업은 Power Query의 열 분할 단계를 만들거나, 구분자 위치가 하나뿐이라면 LEFT·MID·RIGHT·SEARCH·LEN 조합을 사용할 수 있습니다. 구분자 개수가 변하는 데이터를 긴 구형 수식으로 억지로 처리하면 유지보수가 어려워집니다.
값으로 붙여넣어 전달하면 호환성 문제는 줄지만 원본 변경이 반영되지 않습니다. 파일 기준일, 원본 열, 분할 규칙과 수식 사용 버전을 별도 시트에 기록해야 합니다.
실전 검증 시나리오
검증용 표에 정상 행, 구분자가 없는 행, 구분자가 연속된 행, 값 안에 구분자가 들어간 행, 줄바꿈 종류가 다른 행, 항목 수가 부족한 행을 각각 만듭니다. 정상 행은 예상 열 수가 나와야 하고, 연속 구분자는 빈값 보존 정책에 맞아야 하며, 항목 수가 부족한 행은 #N/A 또는 지정한 패딩으로 드러나야 합니다.
주소 사례에서는 서울특별시|중구|세종대로 110처럼 내용에 거의 쓰이지 않는 구분자를 정하고 =TEXTSPLIT(A2,"|")로 나눕니다. 주문 사례에서는 주문번호,품목,수량;A001,노트,2처럼 열과 행 구분자를 분리합니다. 실제 원본에 쉼표가 포함된 품명이 있다면 단순 구조를 쓰지 말고 CSV 인용 규칙을 처리할 수 있는 가져오기 방식을 선택합니다.
분할 결과의 열 개수를 COUNTA로 점검하거나 필수 열이 빈 경우 조건부 서식으로 표시하면 조용한 데이터 손상을 줄일 수 있습니다. 수식이 오류 없이 보인다는 사실만으로 필드가 올바른 위치에 들어갔다고 가정하지 않습니다.
최종 체크리스트
- 열 방향과 행 방향 중 어느 쪽으로 펼칠지 먼저 정했습니다.
- 구분 문자가 실제 데이터 값 안에 포함되는지 검사했습니다.
- 쉼표와 공백, 줄바꿈 문자를 표준화했습니다.
- 연속 구분자 사이 빈 필드를 보존할지 결정했습니다.
- 행별 항목 수 차이를 확인한 뒤 pad_with를 선택했습니다.
- 펼침 결과 주변의 기존 값과 병합 셀을 정리했습니다.
- 숫자·날짜가 텍스트로 남는지 추가 확인했습니다.
- 정상·누락·중복 구분자·불규칙 행으로 실전 테스트했습니다.
- 공동 작업자의 Excel 버전과 구버전 대안을 확인했습니다.
- 복잡한 CSV나 반복 파일은 Power Query를 검토했습니다.
공식 참고 자료
- Microsoft TEXTSPLIT 함수: https://support.microsoft.com/ko-kr/excel/functions/textsplit-function
- Microsoft TEXTBEFORE 함수: https://support.microsoft.com/ko-kr/excel/functions/textbefore-function
- Microsoft TEXTAFTER 함수: https://support.microsoft.com/ko-KR/Excel/textafter-function
- Microsoft 텍스트 나누기 마법사: https://support.microsoft.com/ko-KR/Excel/get-started/split-text-into-different-columns-with-the-convert-text-to-columns-wizard
TEXTSPLIT은 짧은 수식으로 데이터를 나누지만 정확한 결과는 구분자 규칙과 빈값 정책에서 결정됩니다. 원본을 보존하고 열·행 구분자, 연속 구분자, 배열 크기와 버전을 단계별로 검증하면 손으로 복사하는 작업보다 빠르고 재현 가능한 정제 흐름을 만들 수 있습니다.