이 글의 목적은 엑셀에서 CLEAN/TRIM 함수로 공백이나 제어문자를 제거했는데도 텍스트가 정리되지 않을 때, 실제로 발생하는 모든 유형의 공백·특수문자를 진단하고 완벽하게 제거하는 표준 절차와 실무용 공식을 제공하는 것이다.
왜 CLEAN/TRIM이 작동하지 않는가
엑셀의 TRIM은 일반 공백(ASCII 32)을 연속 하나로 축소하고 앞뒤 공백을 제거하는 함수이다. CLEAN은 ASCII 0~31 범위의 인쇄 불가 제어문자를 제거한다. 웹·ERP·CRM에서 가져온 데이터에는 비분리공백(160), 얇은 비분리공백(8239), 제로폭 문자(8203, 65279), 탭(9), 줄바꿈(10, 13) 등 TRIM/CLEAN의 기본 대상이 아닌 코드 포인트가 혼재되어 있는 경우가 많다. 이 문자들은 화면에서는 보이지 않지만 비교·조인·피벗·중복 제거·수치 변환을 방해한다.
흔한 문제 증상
- 같아 보이는 텍스트가 VLOOKUP/XLOOKUP에서 불일치로 처리된다.
- 숫자처럼 보이지만 SUM/AVERAGE가 0 또는 오류를 반환한다.
- 데이터 정렬 시 예상과 다른 순서가 나타난다.
- 중복 제거가 동작하지 않는다.
- 텍스트 길이(LEN)와 화면 문자 수가 일치하지 않는다.
진단: 문제 문자 찾기 체크리스트
- 길이 비교로 존재 여부 확인하다.
=LEN(A2) & " / " & LEN(TRIM(CLEAN(A2)))두 값이 다르면 숨은 문자가 있는 것이다.
- 문자코드 스캐너로 첫 번째/마지막/모든 문제 코드 확인하다.
=IFERROR(UNICODE(LEFT(A2,1)),"") '첫 문자 코드 =IFERROR(UNICODE(RIGHT(A2,1)),"") '마지막 문자 코드동적 배열을 사용해 전체 코드를 나열하면 원인이 명확해진다.
=LET(t,A2, arr, TEXTSPLIT(t,,SEQUENCE(LEN(t))), MAP(arr, LAMBDA(ch, IFERROR(UNICODE(ch), "")))) - 비가시 문자만 하이라이트하다.
=LET(t,A2, s,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(t,CHAR(10),"⏎"),CHAR(13),"␍"),CHAR(9),"⇥"), s)표시용 기호로 치환하여 육안으로 위치를 확인한다.
문제 문자 유형과 제거 전략 요약
| 유형 | 코드 포인트 | 특징 | 기본 함수로 제거 여부 | 대응 공식 |
|---|---|---|---|---|
| 일반 공백 | 32 | 보통 스페이스 | TRIM 가능 | TRIM |
| 비분리공백(NBSP) | 160 | HTML | 불가 | SUBSTITUTE(,CHAR(160)," ") |
| 얇은 비분리공백(NNBSP) | 8239 | 프랑스어 구분자 | 불가 | SUBSTITUTE(,UNICHAR(8239)," ") |
| 제로폭 스페이스 | 8203 | 가시화 불가 | 불가 | SUBSTITUTE(,UNICHAR(8203),"") |
| 제로폭 넌조이너 | 8204 | 문자 조합 제어 | 불가 | SUBSTITUTE(,UNICHAR(8204),"") |
| 제로폭 조이너 | 8205 | 문자 조합 제어 | 불가 | SUBSTITUTE(,UNICHAR(8205),"") |
| 바이트 순서 표시(BOM) | 65279 | U+FEFF, 파일 선두 | 불가 | SUBSTITUTE(,UNICHAR(65279),"") |
| 탭 | 9 | 수평 탭 | CLEAN 불가 | SUBSTITUTE(,CHAR(9)," ") |
| 줄바꿈 | 10,13 | LF/CR | 부분적 | SUBSTITUTE(,CHAR(10)," "), SUBSTITUTE(,CHAR(13)," ") |
| 이상 공백(EN/EM SPACE 등) | 8192~8207 | 타이포그래피용 | 불가 | SUBSTITUTE(,UNICHAR(코드)," ") |
주의 : CLEAN은 탭(9)과 NBSP(160), 제로폭 문자(U+200B 등)를 제거하지 못한다. TRIM은 공백 32만 표준화한다.
표준 공식: “보이는 그대로”+“숨어 있는 것까지” 한 번에 제거
다음 LET 기반 공식은 실무에서 마주치는 대다수 숨은 공백·특수문자를 일괄 정규화한다.
=LET( t, A2, s1, SUBSTITUTE(t, CHAR(9), " "), /* 탭 → 공백 */ s2, SUBSTITUTE(s1, CHAR(10), " "), /* LF → 공백 */ s3, SUBSTITUTE(s2, CHAR(13), " "), /* CR → 공백 */ s4, SUBSTITUTE(s3, CHAR(160), " "), /* NBSP → 공백 */ s5, SUBSTITUTE(s4, UNICHAR(8239), " "), /* NNBSP → 공백 */ s6, SUBSTITUTE(s5, UNICHAR(8203), ""), /* zero-width space 제거 */ s7, SUBSTITUTE(s6, UNICHAR(8204), ""), /* ZWNJ 제거 */ s8, SUBSTITUTE(s7, UNICHAR(8205), ""), /* ZWJ 제거 */ s9, SUBSTITUTE(s8, UNICHAR(65279), ""), /* BOM 제거 */ s10, SUBSTITUTE(s9, UNICHAR(8192), " "), s11, SUBSTITUTE(s10, UNICHAR(8193), " "), s12, SUBSTITUTE(s11, UNICHAR(8194), " "), s13, SUBSTITUTE(s12, UNICHAR(8195), " "), s14, SUBSTITUTE(s13, UNICHAR(8196), " "), s15, SUBSTITUTE(s14, UNICHAR(8197), " "), s16, SUBSTITUTE(s15, UNICHAR(8198), " "), s17, SUBSTITUTE(s16, UNICHAR(8200), " "), s18, SUBSTITUTE(s17, UNICHAR(8201), " "), s19, SUBSTITUTE(s18, UNICHAR(8202), " "), TRIM(CLEAN(s19)) )쉼표 대신 세미콜론을 쓰는 지역 설정에서는 ,를 ;로 바꿔 사용한다.
반복 사용을 위한 LAMBDA 사용자 정의 함수
이 공식을 이름 관리자에 등록하면 워크북 어디서나 간단히 호출할 수 있다.
이름: CLEANALL 참조: =LAMBDA(text, LET( t, text, s1, SUBSTITUTE(t, CHAR(9), " "), s2, SUBSTITUTE(s1, CHAR(10), " "), s3, SUBSTITUTE(s2, CHAR(13), " "), s4, SUBSTITUTE(s3, CHAR(160), " "), s5, SUBSTITUTE(s4, UNICHAR(8239), " "), s6, SUBSTITUTE(s5, UNICHAR(8203), ""), s7, SUBSTITUTE(s6, UNICHAR(8204), ""), s8, SUBSTITUTE(s7, UNICHAR(8205), ""), s9, SUBSTITUTE(s8, UNICHAR(65279), ""), s10, SUBSTITUTE(s9, UNICHAR(8192), " "), s11, SUBSTITUTE(s10, UNICHAR(8193), " "), s12, SUBSTITUTE(s11, UNICHAR(8194), " "), s13, SUBSTITUTE(s12, UNICHAR(8195), " "), s14, SUBSTITUTE(s13, UNICHAR(8196), " "), s15, SUBSTITUTE(s14, UNICHAR(8197), " "), s16, SUBSTITUTE(s15, UNICHAR(8198), " "), s17, SUBSTITUTE(s16, UNICHAR(8200), " "), s18, SUBSTITUTE(s17, UNICHAR(8201), " "), s19, SUBSTITUTE(s18, UNICHAR(8202), " "), TRIM(CLEAN(s19)) ) )사용 예:
=CLEANALL(A2)숫자 변환 이슈 동시 해결 템플릿
숫자처럼 보이는 텍스트에 얇은 공백 또는 그룹 구분 기호가 섞인 경우 다음 템플릿으로 정규화 후 숫자화한다.
=LET( c, CLEANALL(A2), c1, SUBSTITUTE(c, ",", ""), /* 쉼표 제거(필요 시 지역 맞춤) */ c2, SUBSTITUTE(c1, UNICHAR(8239), ""), /* 얇은 비분리공백 제거 */ VALUE(c2) /* 숫자 변환 */ )주의 : 지역 설정에서 소수점이 콤마인 경우 쉼표 제거는 주의하여 적용해야 한다. 그룹 구분과 소수 구분을 명확히 구분하여 치환 순서를 설계해야 한다.
대용량 데이터 실무 절차(컬럼 단위)
- 원본 보존: 원본 열을 복제하다.
- 진단: LEN, UNICODE 스캐닝으로 문제 코드 범주를 결정하다.
- 정규화: CLEANALL 또는 표준 공식을 전체 범위에 적용하다.
- 값 고정: 결과를 복사하여 붙여넣기 값으로 고정하다.
- 숫자화: 필요한 열은 VALUE, -- 연산으로 숫자 변환을 완료하다.
- 검증: 중복 제거, 조인 테스트, 합계 검증으로 품질을 확인하다.
Power Query로 원천에서 정리하기
반복 수집형 데이터는 Power Query 단계에 정리 로직을 포함하는 것이 유지보수 비용을 낮춘다.
- 데이터 탭 → 데이터 가져오기 또는 테이블/범위에서 → Power Query 편집기 열기.
- 대상 열 선택 → 변환 탭 → 형식 → 정리 → 공백 제거 순서로 적용하다.
- 홈 → 값 바꾸기에서 고급 옵션을 열고,
Ctrl+J로 LF 입력,Alt+0010등으로 제어문자를 명시 입력하여 공백으로 치환하다. - 열 추가 → 사용자 지정 열에서 M식으로 제로폭 문자 제거 로직을 추가하다.
M 코드 예시:
= Table.TransformColumns( Source, { {"TextCol", each let t1 = Text.Replace(_, Character.FromNumber(9), " "), t2 = Text.Replace(t1, Character.FromNumber(10), " "), t3 = Text.Replace(t2, Character.FromNumber(13), " "), t4 = Text.Replace(t3, Character.FromNumber(160), " "), t5 = Text.Remove(t4, {Character.FromNumber(8203), Character.FromNumber(8204), Character.FromNumber(8205), Character.FromNumber(65279)}), t6 = List.Accumulate({8192..8202}, t5, (state, c) => Text.Replace(state, Character.FromNumber(c), " ")) in Text.Clean(Text.Trim(t6)) , type text} } )VBA로 정규식 기반 일괄 정리
구버전 엑셀이나 정규식이 필요한 상황에서는 다음 VBA 함수를 표준화하여 사용한다.
Option Explicit
Function CleanAllVBA(ByVal s As String) As String
Dim re As Object
Set re = CreateObject("VBScript.RegExp")
'모든 유니코드 공백(분리/비분리) 및 제어문자 제거/변환
'패턴 설명:
' \x{0009}\x{000A}\x{000D} 탭/LF/CR
' \x{00A0} NBSP
' \x{2000}-\x{200B} 다양한 유니코드 스페이스 및 제로폭
' \x{202F} 얇은 비분리공백
' \x{2060} WORD JOINER
' \x{FEFF} BOM
re.Pattern = "[\x{0009}\x{000A}\x{000D}\x{00A0}\x{2000}-\x{200B}\x{202F}\x{2060}\x{FEFF}]"
re.Global = True
re.IgnoreCase = True
re.Multiline = True
Dim t As String
t = s
'문단 구분자는 공백으로 표준화
t = Replace(t, vbCrLf, " ")
t = Replace(t, vbLf, " ")
t = Replace(t, vbCr, " ")
'유니코드 공백군과 제로폭 제거
t = re.Replace(t, " ")
'여분 공백 축소
t = WorksheetFunction.Trim(t)
CleanAllVBA = t
End Function워크시트에서 =CleanAllVBA(A2)로 호출한다. 이 함수는 시스템에 VBScript.RegExp가 제공될 때 동작한다.
데이터 유효성·조인 전 전처리 파이프라인
=LET(raw, A2, t, CLEANALL(raw), n, IFERROR(VALUE(SUBSTITUTE(t,",","")), t), n)위 파이프라인은 텍스트 정리 후 숫자 변환을 시도하고 실패 시 원래 텍스트를 반환한다. 조인 키는 항상 정규화된 열을 생성하여 사용한다.
성능 최적화
- 동적 배열로 한 셀에 수식 한 번만 계산하고 결과를 spill 하다.
- 중간 결과를 값으로 고정하여 재계산 부담을 줄이다.
- 대량 데이터는 Power Query에서 처리하고, 워크시트에는 결과만 로드하다.
- 필요한 코드 포인트만 단계적으로 치환하여 SUBSTITUTE 체인을 최소화하다.
품질 검증 스크립트
정규화 전후 동등성, 길이, 숫자 변환 가능성을 한 번에 검증하는 보고 열을 생성한다.
=LET( a, A2, b, CLEANALL(A2), ok_equal, a=b, len_before, LEN(a), len_after, LEN(b), as_num, IFERROR(VALUE(b),""), can_num, ISNUMBER(as_num), ok_equal & "|" & len_before & "→" & len_after & "|" & can_num )자주 하는 실수와 회피법
- TRIM만으로 해결하려 한다. 반드시 NBSP, 제로폭, BOM을 점검한다.
- 치환 순서를 고려하지 않는다. 줄바꿈은 먼저 공백으로 바꾼 후 TRIM한다.
- 숫자화에서 그룹 구분 제거와 소수 구분 충돌을 혼동한다. 지역 설정을 확인한다.
- 원본을 덮어쓴다. 정규화 전 열 복제와 감사용 로그를 유지한다.
응용: 텍스트 분리·병합과의 조합
TEXTSPLIT/TEXTBEFORE/TEXTAFTER 사용 전 CLEANALL을 먼저 적용하면 예기치 않은 빈 항목이나 분리 실패를 방지한다.
=TEXTSPLIT(CLEANALL(A2), ",")현장 체크리스트
| 항목 | 점검 방법 | 합격 기준 |
|---|---|---|
| 숨은 문자 존재 | LEN vs LEN(TRIM(CLEAN)) 비교 | 동일해야 한다 |
| NBSP 포함 | FIND(CHAR(160)) | 없어야 한다 |
| 제로폭 문자 | UNICODE 스캔 | 없어야 한다 |
| BOM 제거 | LEFT 코드 포인트 확인 | U+FEFF 불검출 |
| 숫자 변환 성공 | ISNUMBER 검사 | TRUE |
FAQ
TRIM과 CLEAN을 같이 쓰면 충분하지 않은가
두 함수는 ASCII 기반이다. NBSP(160), 얇은 NBSP(8239), 제로폭(8203 계열), BOM(65279) 등은 제거하지 못한다. 표준화 공식이나 LAMBDA를 사용해야 한다.
왜 LEN이 다르게 나오나
LEN은 유니코드 코드 포인트 수를 반환한다. 보이지 않는 문자도 길이에 포함되므로 TRIM/CLEAN 후 길이가 줄어드는 것이 정상이다.
숫자 변환이 계속 실패한다
보이지 않는 공백 외에 그룹 구분 기호, 비ASCII 마이너스(U+2212)와 같은 유사 문자도 제거해야 한다. SUBSTITUTE(,UNICHAR(8722),"-")를 추가한다.
Power Query 정리와 워크시트 수식 중 무엇을 우선하나
반복 처리와 대량 데이터는 Power Query가 안정적이다. 단발성 정제나 사용자 주도 필터링은 워크시트 수식이 빠르다.
Office 2016 등에서 UNICHAR/UNICODE가 없으면
VBA 함수 CleanAllVBA를 사용하거나 Power Query로 처리한다.
CSV 가져오기 시 매번 같은 문제가 생긴다
쿼리 단계에서 정규화 규칙을 고정하고, 가져오기 연결을 새로 고침하도록 설정한다. BOM 처리 옵션을 확인한다.