Excel · 텍스트

엑셀 CLEAN

텍스트에서 눈에 보이지 않는 제어문자(줄바꿈·탭·CHAR 0~31)를 제거합니다. TRIM 짝꿍으로 데이터 정리의 표준.

CLEAN 수식 구조

=CLEAN(text)

text

정리할 텍스트. 셀 참조·문자열 모두 OK. 숫자가 들어가면 자동 텍스트 변환.

▸ 제거하는 것: ASCII CHAR 0~31 (줄바꿈 LF=10, 캐리지리턴 CR=13, 탭 TAB=9, NULL=0, BELL=7 등)

▸ 제거하지 않는 것: 일반 공백(32), 한글, 영문, 유니코드 문자

▸ 주의: 비분할 공백(non-breaking space, CHAR 160)은 ASCII가 아니므로 안 잡힘 → SUBSTITUTE 필요

직접 체험해보세요

샘플 버튼이나 직접 입력으로 비인쇄 문자가 어떻게 제거되는지 확인 — 길이 차이와 마커로 시각화.

B2=CLEAN(A2)

원본 (Before)

19자 · 숨겨진 2개
안녕하세요⏎
반갑습니다⏎
잘 부탁드려요

정리 후 (After)

17자 · 숨겨진 0개
안녕하세요반갑습니다잘 부탁드려요

제거된 비인쇄 문자 → 2자

2자 제거

⏎ = 줄바꿈(CHAR 10), → = 탭(CHAR 9), [CHARn] = 기타 제어문자

TRIM vs CLEAN

둘 다 데이터 정리 함수이지만 대상이 완전히 다름 — 공백은 TRIM, 제어문자는 CLEAN.

공백TRIM
=TRIM(" Hello World ")
→ "Hello World"
  • 일반 공백만 정리
  • 앞·뒤 공백 + 중간 중복 공백
  • 줄바꿈·탭·제어문자는 그대로
비인쇄CLEAN
=CLEAN("A⏎B→C")
→ "ABC"
  • CHAR 0~31 제어문자 제거
  • 줄바꿈·탭·NULL·BELL 등
  • 일반 공백은 그대로 유지

실전 활용 예제

=CLEAN(A2)

셀의 비인쇄 문자만 제거. 웹·CSV에서 붙여넣은 데이터에 흔히 섞이는 줄바꿈·탭을 한 번에 정리.

예시: "안녕⏎세상" → "안녕세상"
=TRIM(CLEAN(A2))

데이터 정리의 표준 콤보. CLEAN으로 제어문자 제거 후 TRIM으로 남은 중복 공백 정리.

예시: " A⏎ B " → "A B"
=SUBSTITUTE(CLEAN(A2), CHAR(160), "")

비분할 공백까지 제거. CLEAN이 못 잡는 CHAR(160)을 SUBSTITUTE로 보완. 웹 크롤링 데이터 정리에 자주 등장.

예시: "A B" → "AB"
=IF(LEN(A2)=LEN(CLEAN(A2)), "깨끗함", "비인쇄 문자 있음")

데이터 검수 트릭. CLEAN 전후 길이가 다르면 비인쇄 문자가 있다는 신호.

예시: 원본 길이 ≠ 정리 길이 → "비인쇄 문자 있음"

다른 엑셀 함수

TRIM (공백 제거)SUBSTITUTE (내용 치환)LEN (글자수)LEFT (왼쪽)
목차▾
  1. 1.엑셀 CLEAN 함수란?
  2. 2.제거되는 문자 / 제거 안 되는 문자
  3. 3.CLEAN + TRIM + SUBSTITUTE 표준 콤보
  4. 4.실전 사용 시나리오 8가지
  5. 5.CLEAN vs TRIM vs SUBSTITUTE
  6. 6.자주 발생하는 오류·함정 6가지
  7. 7.자주 묻는 질문

엑셀 CLEAN 함수란?

엑셀 CLEAN 함수는 텍스트에서 인쇄할 수 없는 제어 문자(ASCII 코드 0~31)를 모두 제거하는 텍스트 정리 함수입니다. 외부 데이터를 복사·임포트했을 때 눈에 안 보이는 줄바꿈·탭·제어 문자가 섞여 VLOOKUP 매칭이 실패하거나 데이터가 깨지는 경우의 80%를 해결합니다.

구문은 =CLEAN(텍스트) 1인수로 단순. 단독으로 쓰는 경우는 드물고 보통 TRIM·SUBSTITUTE와 콤보로 사용합니다. 웹·CSV· 외부 API에서 가져온 데이터의 정합성 정리 표준이에요.

제거되는 문자 / 제거 안 되는 문자

CLEAN의 범위는 명확합니다. ASCII 0~31만 제거.

제거됨 (ASCII 0~31)

코드문자의미
9TAB탭
10LF줄바꿈 (Unix)
13CR캐리지 리턴 (Windows 줄바꿈은 CR+LF)
0NULL널

제거 안 됨 ⚠️

코드문자비고
32일반 공백TRIM으로 제거
160비분할 공백 ( )웹 데이터에 자주, SUBSTITUTE로 제거
기타 Unicodezero-width space 등개별 SUBSTITUTE 필요

CLEAN + TRIM + SUBSTITUTE 표준 콤보

CLEAN 단독으로는 부족할 때가 많아 3종 콤보가 표준 데이터 정리 패턴입니다.

=TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " ")))

처리 순서:

  • 1. SUBSTITUTE: CHAR(160) 비분할 공백 → 일반 공백
  • 2. CLEAN: 제어 문자 (ASCII 0~31) 제거
  • 3. TRIM: 앞뒤 공백 + 중간 다중 공백 정리

웹에서 복사·CSV 임포트·외부 API 데이터 정리의 표준 콤보. 외부 데이터 정합성 99% 해결.

실전 사용 시나리오 8가지

  • 줄바꿈 제거 — =CLEAN(A1) — Alt+Enter 줄바꿈 일괄
  • 표준 데이터 정리 — =TRIM(CLEAN(A1))
  • 비분할 공백까지 — =TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " ")))
  • VLOOKUP 매칭 실패 해결 — =VLOOKUP(TRIM(CLEAN(A1)), 테이블, 2, FALSE)
  • CSV 임포트 후 일괄 정리 — =ARRAYFORMULA(CLEAN(A1:A1000))
  • 전체 시트 적용 — 보조 열에 CLEAN → 결과 복사 → "값만 붙여넣기" → 보조 열 삭제
  • CODE로 비인쇄 문자 탐지 — =CODE(MID(A1, N, 1)) — N번째 문자 ASCII
  • LEN 차이로 비인쇄 문자 카운트 — =LEN(A1)-LEN(CLEAN(A1))

CLEAN vs TRIM vs SUBSTITUTE

함수제거 대상
CLEANASCII 0~31 비인쇄 문자 (줄바꿈·탭 등)
TRIM앞뒤 공백 + 중간 다중 공백 (한 칸으로)
SUBSTITUTE지정한 특정 문자/문자열
CLEAN + TRIM비인쇄 + 공백 모두
+ SUBSTITUTE(CHAR(160))위 + 비분할 공백 — 최강 콤보

자주 발생하는 오류·함정 6가지

  • CHAR(160) 비분할 공백 — 웹·HTML 복사 데이터에 흔함. CLEAN이 못 잡음. 해결: SUBSTITUTE 먼저
  • Unicode 비인쇄 문자 — CHAR(8203) zero-width space, CHAR(65279) BOM 등. 개별 SUBSTITUTE
  • CLEAN 결과가 동일해 보임 — 시각적으로 같지만 =LEN(A1)=LEN(CLEAN(A1))이 FALSE면 있음
  • 탭이 사라져 데이터 깨짐 — 탭 구분 데이터에 CLEAN 적용 시 모두 붙음. 사전에 SUBSTITUTE로 탭 → 다른 구분자
  • TRIM과 순서 — TRIM(CLEAN(A1)) 관행
  • CLEAN은 Unicode 일부만 — Excel 2010+ Unicode 비인쇄 일부 제거, 모든 Unicode 아님

자주 묻는 질문

Q. CLEAN 함수 사용법은?

=CLEAN(텍스트 또는 셀) 형식입니다. 예: =CLEAN(A1). ASCII 코드 0~31 비인쇄 문자(줄바꿈, 탭, 제어 문자 등)를 모두 제거합니다. 보이지 않는 문자로 인한 매칭 실패·데이터 정합성 문제 해결에 표준.

Q. CLEAN과 TRIM의 차이는?

CLEAN은 비인쇄 문자(줄바꿈·탭 등 ASCII 0~31), TRIM은 공백(앞뒤 + 중간 다중 공백). 둘 다 필요할 때가 많아 =TRIM(CLEAN(A1)) 콤보가 표준 데이터 정리 패턴입니다.

Q. CHAR(160) 비분할 공백은 어떻게 제거하나요?

CLEAN은 ASCII 32 미만만 제거하므로 CHAR(160) 비분할 공백은 못 잡습니다. =TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " "))) 콤보로 일반 공백으로 먼저 바꾼 뒤 정리하세요. 웹·HTML 복사 데이터에 흔합니다.

Q. VLOOKUP 매칭이 자꾸 실패해요.

보이지 않는 공백·줄바꿈·비인쇄 문자가 원인일 확률이 80%. =VLOOKUP(TRIM(CLEAN(A1)), 테이블, 2, FALSE)로 검색값을 정리해서 다시 시도하세요. 테이블 쪽 데이터에도 같은 정리가 필요할 수 있습니다.

Q. 셀에 비인쇄 문자가 있는지 확인하려면?

=LEN(A1)=LEN(CLEAN(A1)) 결과가 FALSE면 비인쇄 문자 있음. 몇 개인지는 =LEN(A1)-LEN(CLEAN(A1)). 어느 위치에 어떤 문자인지는 =CODE(MID(A1, N, 1)) (N번째 문자 ASCII 코드).

Q. CSV 임포트 후 데이터 정리 표준 패턴은?

=TRIM(CLEAN(SUBSTITUTE(A1, CHAR(160), " "))) 콤보를 보조 열에 적용 → 결과 복사 → 원본에 "값만 붙여넣기" → 보조 열 삭제. 외부 데이터 정합성 99% 해결.

Q. CLEAN 적용 후에도 결과가 똑같아 보이는데요?

비인쇄 문자가 없거나, Unicode 비인쇄 문자(CHAR(8203) zero-width space 등)라서 CLEAN이 못 잡은 경우. 확인은 LEN 비교, 해결은 해당 Unicode 문자를 SUBSTITUTE로 명시 제거.

Q. 구글 스프레드시트에서도 동일하게 작동하나요?

네. CLEAN은 표준 텍스트 함수로 Google Sheets, LibreOffice Calc, Apple Numbers, 한컴 한셀, WPS에서 동일하게 작동합니다. ARRAYFORMULA(CLEAN(A1:A1000)) 같은 배열 적용도 가능.

본 페이지는 Microsoft Support 공식 CLEAN·TRIM·SUBSTITUTE 문서를 기준으로 작성되었습니다. 정확한 동작은 Excel 버전·환경에 따라 달라질 수 있으니 중요한 작업 전에는 실제 환경에서 확인하세요.