엑셀 CLEAN 함수 – 보이지 않는 특수문자 깔끔하게 제거하기
엑셀 데이터를 다루다 보면 “보이지 않는데 이상한 오류”가 생기는 경우가 많습니다. 특히 아래 같은 경험을 해보신 분들, 모두 CLEAN 함수가 꼭 필요한 상황입니다.
- 셀 안에서 커서가 줄바꿈 되는 것처럼 움직인다.
- VLOOKUP이나 MATCH가 값을 정상적으로 찾지 못한다.
- 필터, 정렬, 피벗에서 일부 값이 묶이지 않는다.
- 겉보기에는 멀쩡한데 글자가 이상하게 붙지 않거나 띄어쓰기가 어색하다.
이 문제들의 원인은 대부분 보이지 않는 특수문자(제어문자)입니다. 이를 한 번에 해결해주는 함수가 바로 CLEAN 함수입니다.
1. CLEAN 함수란?
CLEAN 함수는 텍스트 안에 포함된 줄바꿈, 제어문자, 보이지 않는 특수문자를 제거해주는 함수입니다. 외부 시스템(ERP, POS, 앱 로그, 웹 크롤링 데이터)에서 받은 자료에 특히 많이 포함되는 문제 문자들을 정리할 때 유용합니다.
함수 구조
=CLEAN(텍스트)엑셀에서 지원하지 않는 0~31번 제어문자를 모두 제거해 텍스트를 ‘정상화’하는 기능을 합니다.
2. CLEAN으로 제거할 수 있는 문자
대표적으로 다음 같은 보이지 않는 문자들을 제거합니다.
- 줄바꿈 문자(LF: Line Feed)
- 탭 문자(Tab)
- 문자열 사이에 삽입된 제어문자
- 웹 크롤링 시 가져와지는 특수 공백
- ERP 시스템에서 포함되는 숨은 문자
이 문자들은 눈에 보이지 않지만 엑셀 내부에서는 “다른 문자”로 취급되어 정렬·검색·필터링·수식 처리에서 오류를 발생시킵니다.
3. CLEAN 함수 기본 사용 예제
① 줄바꿈 제거
A1에 다음 데이터가 있다고 가정해봅니다:
홍길동
팀장
(ALT + ENTER로 줄바꿈된 상태)
이럴 때:
=CLEAN(A1)결과 → "홍길동팀장"
② 웹에서 복사한 텍스트 정리
웹에서 복사한 문장은 보이지 않는 특수문자가 많이 섞여 있습니다.
A1 = "서울특별시 강남구" (중간에 보이지 않는 제어문자 존재)
=CLEAN(A1)결과 → "서울특별시 강남구"
③ POS/ERP 다운로드 데이터 오류 해결
시스템에서 내려받은 데이터는 종종 LF/LB 같은 특수 문자가 숨어 있어 피벗이나 텍스트 분리에 오류를 일으킵니다.
이때 CLEAN을 적용하면 대부분 즉시 해결됩니다.
4. TRIM + CLEAN 조합 = 최강 데이터 정리 도구
CLEAN은 “숨겨진 문자”를 제거하지만, 일반 공백은 제거하지 않습니다. 반대로 TRIM은 공백은 정리하지만 특수문자를 못 제거할 때가 있습니다.
그래서 실무에서는 이 두 가지를 함께 사용하는 것이 정답입니다.
실무 최강 조합
=TRIM(CLEAN(A1))이 공식은 공백 + 특수문자 + 줄바꿈까지 한 번에 처리하며 대량 데이터 정리에 가장 널리 사용되는 방식입니다.
5. CLEAN으로 제거되지 않는 문자도 있다
중요한 점은 CLEAN이 모든 특수문자를 제거하는 것은 아니라는 것입니다. 특히 가장 문제를 많이 일으키는 특수공백인 CHAR(160)은 CLEAN으로 제거되지 않습니다.
해결법
=TRIM(SUBSTITUTE(A1, CHAR(160), " "))이 공식을 사용하면 비표준 공백을 일반 공백으로 전환한 뒤 TRIM으로 정리할 수 있습니다.
6. 실무 예제 정리
① 고객명부 정리
특수문자 때문에 피벗이 제대로 묶이지 않을 때:
=CLEAN(A1)② 제품코드 줄바꿈 제거
ERP나 모바일에서 내려받은 코드가 줄바꿈 포함 시:
=CLEAN(A1)③ 설문조사 CSV 불필요 문자 제거
줄바꿈 문자 때문에 데이터가 두 줄로 표시되는 문제 해결:
=CLEAN(A1)④ 메모 앱 내용 정리
스마트폰 메모 복사 → 엑셀 붙여넣기 시 줄바꿈과 공백 문제 제거:
=TRIM(CLEAN(A1))7. CLEAN 함수 사용 시 주의할 점
- 특수문자를 모두 제거하면 문장 구조가 달라질 수 있다
- 완전 자동으로 해결되지 않을 수 있음 → SUBSTITUTE와 병행 권장
- 대규모 데이터에 적용 시 계산량이 많아질 수 있음
- HTML 태그나 웹코드 형태는 제거하지 못함
정리
CLEAN 함수는 엑셀에서 나타나는 “눈에 보이지 않지만 매우 귀찮은 문제들”을 가장 빠르고 안전하게 해결해주는 필수 함수입니다.
특히 외부 자료를 자주 다루는 직장인이라면 TRIM과 함께 꼭 익혀야 하는 데이터 전처리의 핵심 기술입니다.
다음 글에서는 CLEAN과 함께 자주 사용되는 SUBSTITUTE 함수(특정 문자 자동 치환)를 작성해드릴까요?
원하시면 다음 주제로 바로 이어서 작성해드리겠습니다!
0 댓글