엑셀에서 공백·특수문자 제거 시 CLEAN/TRIM이 작동 안 할 때

 

엑셀에서 데이터를 관리하거나 분석할 때 텍스트 데이터 내의 불필요한 공백이나 특수문자를 제거하는 것은 필수적인 작업입니다. 흔히 사용하는 CLEAN과 TRIM 함수가 때로 제대로 작동하지 않아 어려움을 겪는 경우가 많습니다. 이번 글에서는 이와 같은 문제가 발생하는 원인을 심층적으로 분석하고, 이를 해결하는 실질적인 방법과 다양한 활용 팁을 안내합니다.

반응형

1. 오류 발생 원인 또는 이유

엑셀의 CLEAN 함수는 ASCII 코드 0~31의 인쇄 불가능한 제어 문자만 제거하는 기능을 합니다. 이는 즉, 유니코드 문자나 특수 HTML 문자와 같은 기타 비표준 문자를 제거하지 못하는 한계가 있습니다. 또한 TRIM 함수는 텍스트 양 끝과 중간에 연속적으로 반복된 표준 공백(ASCII 32번)만 처리할 수 있기 때문에 비표준 공백 문자나 HTML 복사 과정에서 유입된 특수한 공백(NBSP, 제로 너비 공백 등)은 남겨지게 됩니다.

자세한 예시는 아래 표를 통해 살펴볼 수 있습니다.

구분	원인 설명	예시
비표준 공백(NBSP)	ASCII 코드(160)로 표현되는 NBSP(Non-breaking Space)와 같은 비표준 공백은 TRIM으로 제거되지 않습니다.	TRIM("엑셀 테스트") → NBSP 공백이 제거되지 않고 남아있음
유니코드 특수 문자	유니코드 기반 특수문자(예: 제로 너비 공백, 유니코드 줄바꿈)는 CLEAN 함수로 제거되지 않습니다.	CLEAN("엑셀 테스트​") → 제로 너비 공백(\u200B)이 제거되지 않음
HTML/웹 복사 붙여넣기	웹사이트에서 복사한 텍스트를 엑셀에 붙여넣는 경우 HTML 특수문자와 공백이 포함되어 문제가 발생합니다.	웹에서 복사한 내용을 붙여넣었을 때 특수문자가 그대로 남아 있음
데이터 불러오기 문제	CSV나 텍스트 파일을 엑셀로 불러올 때 텍스트 인코딩으로 인해 특수문자나 공백이 추가될 수 있습니다.	CSV 불러오기 후 공백 또는 알 수 없는 특수문자 발생

2. 해결 방법

이 문제를 해결하기 위해서는 다음과 같은 방법을 사용할 수 있습니다.

방법	설명	예시
SUBSTITUTE 함수로 치환	TRIM이 처리하지 못하는 특정 비표준 공백(NBSP)을 직접 SUBSTITUTE로 제거합니다.	=TRIM(SUBSTITUTE(A1,CHAR(160),""))
UNICODE/UNICHAR 활용 제거	SUBSTITUTE 함수와 함께 UNICODE 문자 코드를 활용하여 특수문자를 제거합니다.	=SUBSTITUTE(A1,UNICHAR(8203),"")
VBA 사용자 정의 함수(UDF) 활용	VBA를 통해 사용자 정의 함수를 만들어 더 다양한 공백과 특수문자를 제거할 수 있습니다.	VBA 코드: Range("A1").Value = Replace(Range("A1").Value, ChrW(8203), "")
텍스트 나누기 및 병합	데이터를 다시 구분자로 나눈 뒤 병합하는 방식으로 숨겨진 특수 문자를 제거합니다.	텍스트 나누기(공백 또는 특수문자 기준) 후 다시 결합하여 처리

 

3. 팁과 예방 방법

엑셀 데이터 작업 중 발생할 수 있는 특수문자 문제를 사전에 예방하거나 보다 쉽게 처리할 수 있는 방법은 다음과 같습니다.

팁 및 예방방법	상세 설명	적용 예시
"텍스트로 붙여넣기" 활용	웹에서 복사한 텍스트를 붙여넣을 때 엑셀의 "텍스트로 붙여넣기" 기능을 사용합니다.	웹 브라우저에서 복사 후 엑셀에서 "텍스트로 붙여넣기"로 처리하기
정규 표현식 및 Power Query	VBA나 Power Query를 이용한 정규 표현식 처리를 통해 다양한 특수문자를 효율적으로 제거할 수 있습니다.	VBA 예시: RegEx.Pattern = "[\u200B\u00A0]"
데이터 임포트 옵션 설정	CSV나 텍스트 파일을 불러올 때 텍스트 한정자나 구분자를 정확히 설정해 불필요한 문자를 방지합니다.	CSV 가져오기 시 텍스트 구분자 설정 및 데이터 유형 정확히 설정하기

 

 

4. FAQ

Q. TRIM 함수가 제거하지 못하는 공백 유형은 무엇인가요?

엑셀 TRIM 함수는 ASCII 코드 32번의 표준 공백만 제거합니다. 따라서 NBSP(ASCII 코드 160), 제로 너비 공백(유니코드 \u200B)과 같은 공백은 제거하지 못합니다.

Q. CLEAN 함수가 제거 가능한 문자 범위는 무엇인가요?

엑셀 CLEAN 함수는 ASCII 코드값 0~31의 비인쇄 제어 문자만 제거할 수 있으며, 유니코드 특수문자, HTML 문자 등 다른 문자는 제거하지 못합니다.

Q. SUBSTITUTE 함수를 자주 추천하는 이유는 무엇인가요?

SUBSTITUTE 함수는 특정 문자나 공백을 지정하여 정확히 제거 또는 교체할 수 있기 때문에 TRIM이나 CLEAN 함수로 처리하지 못하는 다양한 문자들을 보다 유연하게 처리할 수 있기 때문입니다.

Q. VBA를 통한 처리가 더 효과적인 이유는 무엇인가요?

VBA를 사용하면 대량의 데이터를 빠르고 유연하게 처리할 수 있으며, 다양한 유형의 특수문자와 공백을 한번에 효과적으로 처리할 수 있습니다.