이 글의 목적은 엑셀의 “텍스트 나누기(구분)” 기능과 CSV·TSV 데이터 분할 과정에서 자주 발생하는 오류의 원인을 체계적으로 진단하고, 실무에서 바로 적용할 수 있는 해결책과 검증 절차를 제공하는 것이다.
1. 텍스트 나누기(구분) 기능의 동작 원리 이해
텍스트 나누기(구분)는 한 셀 또는 붙여넣은 텍스트에서 구분 기호(쉼표, 탭, 세미콜론, 사용자 정의 기호 등) 또는 고정 너비를 기준으로 데이터를 열 단위로 분리하는 기능이다. 핵심 요소는 세 가지이다.
- 구분 기호 선택: 쉼표(,), 탭, 세미콜론(;), 공백, 기타 사용자 정의 문자 등이다.
- 텍스트 한정자(Text Qualifier): 보통 쌍따옴표(")로, 필드 내부의 구분 기호를 일반 문자로 처리하게 한다.
- 데이터 형식 지정: 일반, 텍스트, 날짜(연-월-일 등)로 열별 서식을 지정한다.
주의 : “텍스트 한정자”가 올바르게 인식되지 않으면 따옴표로 감싼 필드 내부의 쉼표가 분리되어 열이 어긋한다.
2. 자주 겪는 증상과 1차 대응표
| 증상 | 원인 | 즉시 대처 |
|---|---|---|
| 따옴표로 감싼 값 내부의 쉼표로 열이 더 생김 | 텍스트 한정자 미설정 또는 데이터에 혼용된 따옴표 | 마법사 2단계에서 텍스트 한정자 " 지정, 혼용된 ‘/”는 사전 정규화 |
| 우편번호·상품코드의 앞자리 0이 사라짐 | 숫자 자동 변환 | 해당 열 서식을 “텍스트”로 지정 또는 사용자 지정 000000 |
| 2025-10-01이 2025년 10월 1일로 날짜화됨 | 자동 날짜 추론 | 열 서식을 “텍스트”로 지정, 필요 시 분리 후 TEXT 함수로 표시 제어 |
| 1,234가 1.234 또는 1234로 바뀜 | 천단위/소수점 구분자 지역 차이 | 데이터->텍스트 나누기 마법사 3단계에서 “고급” 구분자 설정 |
| 한글 깨짐(� 표시) | 파일 인코딩 불일치 | 데이터 가져오기에서 인코딩 UTF-8/ANSI 재지정 또는 Power Query 사용 |
| 연속 공백이 의도치 않게 분리 | 공백을 구분 기호로 지정 | 공백 구분 해제 또는 “연속 구분 기호 하나로 처리” 설정 |
| 필드 끝에 보이지 않는 공백으로 매칭 실패 | 비분리 공백(CHAR(160)) 존재 | 정규화 수행: SUBSTITUTE/ TRIM/ CLEAN 결합 |
| 열 개수 매 줄 불일치 | 누락된 따옴표, 줄바꿈 포함 필드 | 텍스트 한정자 점검, 줄바꿈은 한정자 내 존재해야 정상 파싱 |
3. 안전한 작업 절차: 원본 보존→사전 정규화→분리→검증
- 원본 보존: CSV/TSV 원본을 별도 시트에 그대로 보관한다.
- 사전 정규화: 따옴표·공백·줄바꿈·인코딩 문제를 제거한다.
- 분리 수행: 텍스트 나누기(구분) 또는 Power Query로 분리한다.
- 검증: 열 개수, 합계, 데이터 타입, 고유키 중복을 점검한다.
주의 : 대량 데이터에서 바로 “텍스트 나누기”를 실행하면 되돌리기 어려운 변형이 발생한다. 복사본 시트에서 실험 후 본 시트에 적용한다.
4. 사전 정규화 레시피
4.1 숨은 공백·제어문자 제거
=TRIM(SUBSTITUTE(SUBSTITUTE(CLEAN(A2),CHAR(160)," ")," "," ")) 설명한다. CLEAN은 인쇄 불가 문자를 제거한다. CHAR(160)은 비분리 공백이다. TRIM은 양끝과 중복 공백을 줄인다.
4.2 혼용된 텍스트 한정자 통일
=SUBSTITUTE(SUBSTITUTE(A2,"“",""""),"”","""") 한국어 문서에서 방향따옴표가 섞여 있을 때 “텍스트 한정자" 인식 실패가 발생한다. 위 방식으로 표준 쌍따옴표로 통일한다.
4.3 쉼표/세미콜론 지역 차이 보정
유럽식 CSV는 세미콜론으로 구분하고 소수점은 콤마를 사용한다. 다음처럼 임시 치환으로 혼동을 줄인다.
=SUBSTITUTE(A2,";","|") ' 구분자는 | 로 임시 치환 주의 : 임시 치환은 필드 내부 문자도 함께 바꾼다. 먼저 텍스트 한정자를 정확히 인식시킨 뒤 Power Query에서 “구분 기호로 열 분할”을 사용하면 안전하다.
5. 텍스트 나누기(구분) 마법사 정확 설정
- 범위 선택 후 데이터->텍스트 나누기(구분) 선택한다.
- 1단계: “구분 기호로 분리” 또는 “고정 너비” 선택한다.
- 2단계: 쉼표/탭/세미콜론/공백/기타를 선택한다. “텍스트 한정자”를 " 로 지정한다. “연속 구분 기호 하나로 처리”를 적절히 사용한다.
- 3단계: 열별 데이터 형식을 지정한다. 앞자리 0 보존 열은 “텍스트”로 설정한다. 숫자·날짜 자동 변환을 피하려면 텍스트로 둔다.
주의 : 3단계에서 서식을 “일반”으로 두면 우편번호, 제품코드, 대형 숫자, 날짜 유사 문자열이 자동 변환된다.
6. 앞자리 0, 대형 숫자, ID 보존
6.1 열 서식으로 보존
텍스트 나누기 3단계 → 열 선택 → “텍스트” 6.2 사용자 지정 표시 형식
셀 서식 → 사용자 지정 → 000000 ' 6자리 강제 표시 6.3 변환 후 복구가 필요한 경우
=TEXT(A2,"000000") 주의 : 대형 식별자(예: 16자리 카드 토큰)는 일반 서식에서 지수 표기나 반올림이 발생한다. 반드시 텍스트 서식을 선행한다.
7. 날짜·숫자 지역 설정 충돌 대응
CSV가 “2025-03-07”을 포함할 때 일부 지역 설정에서 3월 7일로, 다른 환경에서는 7월 3일로 해석될 수 있다. 안전책은 다음과 같다.
- 분리 단계에서 해당 열을 “텍스트”로 지정한다.
- 표준화된 ISO 8601(YYYY-MM-DD) 문자열을 유지한 뒤 보고 시점에만 표시 형식을 적용한다.
- 필요 시 “고급” 옵션에서 천단위/소수 구분 기호를 명시한다.
=DATE(VALUE(LEFT(A2,4)),VALUE(MID(A2,6,2)),VALUE(RIGHT(A2,2))) 8. 따옴표와 내장 줄바꿈이 있는 CSV
CRM·폼 데이터에는 필드 내부 줄바꿈(Alt+Enter)이 존재한다. 올바른 CSV는 이런 줄바꿈을 쌍따옴표로 감싼다. 해결 순서는 다음과 같다.
- 데이터 가져오기에서 “텍스트/CSV에서” 기능을 사용한다.
- 텍스트 한정자 " 인지 여부를 확인한다.
- Power Query에서 “구분 기호로 열 분할”을 선택하고 “따옴표 안의 구분 기호 무시”를 활성화한다.
주의 : 메모장으로 편집 시 한정자 쌍이 깨지면 줄바꿈 경계도 무너진다. 원본을 다시 추출하거나 정규표현식으로 불완전 쌍을 보정한다.
9. 인코딩 문제: 한글 깨짐 방지
CSV가 UTF-8, UTF-8 BOM, ANSI(Windows-1252/949) 등 다양한 인코딩을 가진다. 엑셀에서 파일을 바로 열면 잘못된 코드페이지로 해석될 수 있다. 다음이 안전하다.
- 데이터 탭의 “텍스트/CSV에서”로 가져오기한다.
- 미리보기에서 파일 원본 인코딩을 UTF-8 또는 적합한 코드페이지로 지정한다.
- Power Query 편집에서 필요한 정규화를 수행하고 로드한다.
10. “고정 너비” 분할 정확도 높이기
고정 너비 데이터는 열 경계가 글자수로 확정된다. 혼합 폭 문자를 포함하면 경계가 틀어진다. 사전 패딩을 적용한다.
=TEXT(A2,"@") ' 고정 너비 전에는 고정 폭 폰트에서 경계를 재확인한다. 또는 Power Query에서 “문자 수 기준 분할”을 사용하여 경계를 수치로 정의한다.
11. Power Query 활용: 복원 가능한 파이프라인
- 데이터->데이터 가져오기->텍스트/CSV에서 선택한다.
- 구분 기호, 인코딩, 자료형 자동 변환을 검토한다.
- 열 머리글에서 “열 분할->구분 기호 기준”을 선택하고 “따옴표 안 구분 기호 무시”를 켠다.
- 열 자료형을 “텍스트”로 강제한다.
- 닫기 및 로드한다. 쿼리는 재사용 가능하며 원본 변경 시 새로 고침만 하면 된다.
주의 : Power Query의 자동 자료형 변환이 날짜·숫자로 바꾸는 경우가 있다. 적용 단계에서 “변경된 유형” 단계를 삭제하거나 직접 텍스트로 지정한다.
12. 수식으로 대체: TEXTSPLIT, TEXTBEFORE/AFTER
Microsoft 365에서는 동적 배열 함수를 이용해 마우스 조작 없이 분리한다. 재현성과 문서화에 유리하다.
12.1 기본 분리
=TEXTSPLIT(A2,",") 12.2 따옴표 처리와 여러 구분 기호
=TEXTSPLIT(A2, {",",";","|"},,TRUE) ' 연속 구분 기호 하나로 처리 12.3 좌우 추출
=TEXTBEFORE(A2,",") ' 첫 쉼표 앞 =TEXTAFTER(A2,",") ' 첫 쉼표 뒤 12.4 내부 따옴표 제거
=SUBSTITUTE(TEXTSPLIT(A2,","),"""","") 주의 : TEXTSPLIT은 텍스트 한정자까지 완벽히 지원하지 않는다. 따옴표 안 구분 기호를 보존하려면 Power Query가 더 안전하다.
13. 실무 예제 시나리오별 해결책
13.1 CRM 내보내기 CSV: 주소에 쉼표 포함
"고객명","주소","전화" "홍길동","Seoul, KR","010-1234-5678" 마법사 2단계 텍스트 한정자 " 지정한다. 3단계에서 전화·우편번호 열을 텍스트로 지정한다.
13.2 유럽 숫자 형식
"Item";"Qty";"Price" "A";"10";"1,25" 마법사 3단계 “고급”에서 소수 구분 기호를 , 로, 천 단위 구분 기호를 . 또는 공백으로 지정한다. 또는 Price 열을 텍스트로 가져온 뒤 SUBSTITUTE로 변환한다.
=VALUE(SUBSTITUTE(B2,",",".")) 13.3 은행 거래내역: 앞자리 0과 날짜
계좌번호는 텍스트, 거래일자는 텍스트로 먼저 로드하고 보고서 단계에서 날짜로 변환한다.
14. 대량 데이터 검증 체크리스트
| 검증 항목 | 방법 | 합격 기준 |
|---|---|---|
| 열 개수 일관성 | =LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1로 대조 | 모든 행 동일 |
| 고유키 중복 | 조건부 서식 또는 COUNTIF | 중복 0건 |
| 형식 적합성 | 데이터 유효성 검사 | 숫자·날짜·텍스트 규칙 충족 |
| 합계 일치 | 원본 합계 vs 분리 후 합계 비교 | 차이 0 |
15. 줄바꿈과 공백 처리 모범 규칙
- 데이터 입력 규칙에 공백·따옴표·줄바꿈 금지 또는 형식 정의를 포함한다.
- 입력 단계에서 자동 트림·치환을 수행한다.
- 내보내기는 UTF-8 with BOM 또는 시스템 표준으로 통일한다.
16. 붙여넣기 전 세이프 가드
- 붙여넣기 대상 열 서식을 미리 “텍스트”로 지정한다.
- 클립보드의 탭/쉼표 구조를 메모장 또는 미리보기에서 확인한다.
- 붙여넣기 후 즉시 열 개수, 대표 행을 검증한다.
17. 오류 재현과 역추적 팁
문제를 재현해야 원인을 고정할 수 있다. 다음 절차를 따른다.
- 문제 행을 별도 시트로 추출한다.
- 문자 수, 구분 기호 수, 따옴표 짝 유무를 수식으로 계산한다.
- 텍스트 한정자를 바꿔가며 분리 테스트한다.
- 인코딩을 변경하여 미리보기 결과를 비교한다.
18. VBA로 반복 업무 자동화 예시
Sub SplitCsvSafe() Dim ws As Worksheet, qt As QueryTable, p As String Set ws = ActiveSheet p = "C:\data\input.csv" For Each qt In ws.QueryTables qt.Delete Next qt With ws.QueryTables.Add(Connection:="TEXT;" & p, Destination:=ws.Range("A1")) .TextFilePlatform = 65001 ' UTF-8 .TextFileCommaDelimiter = True ' 콤마 구분 .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileDecimalSeparator = "." ' 소수점 .TextFileThousandsSeparator = "," ' 천단위 .TextFileColumnDataTypes = Array(2, 2, 2) ' 모두 텍스트 .AdjustColumnWidth = False .PreserveFormatting = True .Refresh BackgroundQuery:=False End With End Sub 설명한다. QueryTable을 이용하면 인코딩, 구분 기호, 텍스트 한정자, 열 형식을 고정할 수 있다. 자료형 2는 텍스트를 의미한다.
19. 고급 문제 해결 패턴
19.1 따옴표 깨짐 자동 복구
짝이 맞지 않는 따옴표는 정규식 또는 Power Query “열 병합→재분할”로 식별한다. 임시로 구분 기호를 드문 문자(|)로 대체 후 재분할한다.
19.2 다중 구분 기호 데이터
필드마다 다른 구분 기호를 쓰는 경우 Power Query에서 “스플릿 기준: 구분 기호 목록”과 “열 고급 옵션”을 병용한다. 수식 기반 접근은 TEXTSPLIT에 배열 구분 기호를 제공한다.
19.3 비가시 문자 제거 파이프라인
=LET( t,A2, c,CLEAN(t), nb,SUBSTITUTE(c,CHAR(160)," "), TRIM(SUBSTITUTE(nb,CHAR(9)," ")) ) 20. 배포 전 최종 체크리스트
- 원본 백업 파일과 변환 규칙 문서를 함께 보관한다.
- 변환 단계마다 스냅샷 시트를 저장한다.
- 쿼리 또는 수식 기반으로 재현 가능한 파이프라인을 남긴다.
- 형식 강제(텍스트, 사용자 지정 표시)를 분리 단계에서 적용한다.
FAQ
텍스트 한정자가 없는 CSV는 어떻게 안전하게 나누나?
Power Query에서 구분 기호로 분할 후 열 수가 맞지 않는 행을 필터링하여 예외를 먼저 처리한다. 구분 기호가 필드 내부에도 등장한다면 원본 생성 단계에서 필드 감싸기를 적용하는 것이 근본 해결이다.
앞자리 0 보존과 숫자 계산을 동시에 하고 싶다.
원본 열은 텍스트로 보존하고, 계산용 별도 열에서 VALUE로 숫자로 변환한다. 보고서는 TEXT로 서식을 입힌다.
한글이 깨질 때 가장 빠른 확인법은 무엇인가?
데이터->텍스트/CSV에서 미리보기 인코딩을 UTF-8로 바꿔본다. 정상 표시되면 해당 인코딩으로 로드한다. 아니면 ANSI 계열로 순차 변경한다.
세미콜론 CSV를 쉼표 CSV로 바꾸고 싶다.
Power Query에서 분리 후 다시 CSV로 내보낸다. 텍스트 한정자와 구분 기호를 표준 쉼표로 지정한다.
줄바꿈 포함 메모 필드는 어떻게 유지하나?
반드시 텍스트 한정자 "로 감싸서 내보내야 한다. 가져오기 시에도 한정자를 인식해야 행이 깨지지 않는다.
엑셀 없이 빠르게 구조를 확인하려면?
메모장이나 코드 편집기에서 보이는 특수문자 표시 기능을 활용해 구분 기호, 따옴표 짝, 줄바꿈을 확인한다.