엑셀 파일 호환성 문제 완벽 해결 가이드: 버전·형식·기능 충돌 진단과 복구 방법

날짜:

Contents 호환성 문제의 정의와 증상 분류 즉시 조치 체크리스트(빠른 복구) 파일 형식별 특성과 선택 기준 버전 차이로 인한 기능 손실과 대체 전략 조직 환경 차이: 32/64비트, 추가기능, 보안 정책 CSV·텍스트 파일 호환성: 인코딩·구분 기호·서식 날짜·숫자 변형 방…

글 전체 보기

엑셀 공백 제거 안 될 때 해결: CLEAN, TRIM이 먹히지 않는 숨은 공백·특수문자 완벽 제거 가이드

날짜:

이 글의 목적은 엑셀에서 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)와 화면 문자 수가 일치하지 않는다.

진단: 문제 문자 찾기 체크리스트

  1. 길이 비교로 존재 여부 확인하다. 
    =LEN(A2) & " / " & LEN(TRIM(CLEAN(A2)))

    두 값이 다르면 숨은 문자가 있는 것이다.

  2. 문자코드 스캐너로 첫 번째/마지막/모든 문제 코드 확인하다. 
    =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), ""))))
  3. 비가시 문자만 하이라이트하다. 
    =LET(t,A2, s,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(t,CHAR(10),"⏎"),CHAR(13),"␍"),CHAR(9),"⇥"), s)

    표시용 기호로 치환하여 육안으로 위치를 확인한다.

문제 문자 유형과 제거 전략 요약

유형코드 포인트특징기본 함수로 제거 여부대응 공식
일반 공백32보통 스페이스TRIM 가능TRIM
비분리공백(NBSP)160HTML  불가SUBSTITUTE(,CHAR(160)," ")
얇은 비분리공백(NNBSP)8239프랑스어 구분자불가SUBSTITUTE(,UNICHAR(8239)," ")
제로폭 스페이스8203가시화 불가불가SUBSTITUTE(,UNICHAR(8203),"")
제로폭 넌조이너8204문자 조합 제어불가SUBSTITUTE(,UNICHAR(8204),"")
제로폭 조이너8205문자 조합 제어불가SUBSTITUTE(,UNICHAR(8205),"")
바이트 순서 표시(BOM)65279U+FEFF, 파일 선두불가SUBSTITUTE(,UNICHAR(65279),"")
탭9수평 탭CLEAN 불가SUBSTITUTE(,CHAR(9)," ")
줄바꿈10,13LF/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) /* 숫자 변환 */ )
주의 : 지역 설정에서 소수점이 콤마인 경우 쉼표 제거는 주의하여 적용해야 한다. 그룹 구분과 소수 구분을 명확히 구분하여 치환 순서를 설계해야 한다.

대용량 데이터 실무 절차(컬럼 단위)

  1. 원본 보존: 원본 열을 복제하다.
  2. 진단: LEN, UNICODE 스캐닝으로 문제 코드 범주를 결정하다.
  3. 정규화: CLEANALL 또는 표준 공식을 전체 범위에 적용하다.
  4. 값 고정: 결과를 복사하여 붙여넣기 값으로 고정하다.
  5. 숫자화: 필요한 열은 VALUE, -- 연산으로 숫자 변환을 완료하다.
  6. 검증: 중복 제거, 조인 테스트, 합계 검증으로 품질을 확인하다.

Power Query로 원천에서 정리하기

반복 수집형 데이터는 Power Query 단계에 정리 로직을 포함하는 것이 유지보수 비용을 낮춘다.

  1. 데이터 탭 → 데이터 가져오기 또는 테이블/범위에서 → Power Query 편집기 열기.
  2. 대상 열 선택 → 변환 탭 → 형식 → 정리 → 공백 제거 순서로 적용하다.
  3. 홈 → 값 바꾸기에서 고급 옵션을 열고, Ctrl+J로 LF 입력, Alt+0010 등으로 제어문자를 명시 입력하여 공백으로 치환하다.
  4. 열 추가 → 사용자 지정 열에서 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 처리 옵션을 확인한다.

추천·관련글