엑셀에서 셀의 공백 제거(TRIM 함수) 활용법

데이터 입력 과정에서 발생한 과도한 공백과 보이지 않는 제어 문자는 집계 함수와 조회 함수 정확도를 떨어뜨린다. 본 문서는 TRIM 함수를 중심으로 CLEAN·SUBSTITUTE·TEXTJOIN을 결합하여 공백 문제를 체계적으로 제거하는 기법을 제시하고, VBA·Power Query·동적 배열 함수까지 확장하여 완전 자동화를 구현하는 방법을 안내한다.

1. TRIM 함수의 기본 구조와 한계

=TRIM(text) 함수는 문자열의 선행·후행 공백과 중복된 내부 공백을 하나로 줄인다. 그러나 ANSI 160(줄바꿈 없는 공백)과 제어 문자(CHAR 9, 10, 13)는 제거하지 못한다. 실무에서는 TRIM 단독 사용만으로 정확도를 담보하기 어렵다.

문자 종류	ASCII 코드	TRIM 제거 여부	실무 영향
일반 공백	32	제거된다	대부분 해결된다
줄바꿈 없는 공백	160	제거되지 않는다	숫자·텍스트 불일치 발생이다
탭	9	제거되지 않는다	피벗 필터 불량이다
줄바꿈(개행)	10·13	제거되지 않는다	VLOOKUP 실패이다

2. TRIM+CLEAN 조합으로 제어 문자 제거하기

제어 문자를 동시에 처리하려면 다음 구문을 사용한다.

=TRIM(CLEAN(A2))

CLEAN은 0~31번 제어 문자를 삭제한다. 하지만 160은 여전히 남는다. 이를 위해 SUBSTITUTE를 추가한다.

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

이 공식은 다음 세 단계로 작동한다.

1. SUBSTITUTE가 CHAR(160)을 일반 공백으로 치환하다.
2. CLEAN이 제어 문자를 제거하다.
3. TRIM이 남은 불필요 공백을 정리하다.

3. 대량 데이터 정제: 동적 배열 & TEXTJOIN 활용

3-1) 다중 셀 일괄 TRIM

엑셀 365 이상 버전에서는 =MAP() 함수로 범위 전체를 한 번에 TRIM 처리할 수 있다.

=MAP(A2:A1000,LAMBDA(x, TRIM(CLEAN(SUBSTITUTE(x,CHAR(160)," ")))))

3-2) 텍스트 결합 시 공백 자동 관리

TEXTJOIN은 구분 기호를 지정해 결합 전 공백을 제거한다.

=TEXTJOIN(" ",TRUE,TRIM(A2),TRIM(B2),TRIM(C2))

두 번째 인수 TRUE는 빈 셀을 무시하여 이중 공백 발생을 차단한다.

4. 실무 예제: 고객 주소 데이터 클렌징 프로젝트

4-1) 프로젝트 배경

CRM 시스템에서 추출한 고객 주소는 탭·개행·ANSI 160 공백이 혼재되어 우편 자동화 소프트웨어가 인식하지 못하는 문제가 발생하였다. 총 12만 행 데이터 처리 시간이 SLA를 위협하였다.

4-2) 솔루션 설계

1. Power Query로 CSV 파일을 로드하다.
2. 열 추가 > 사용자 지정 열에서 다음 M코드를 작성하다.
   
   =Text.Trim(Text.Clean(Text.Replace([Address]," "," ")))
3. 데이터를 테이블로 로드 후 VBA 매크로로 TRIM 상태 검증 리포트를 생성하다.

4-3) 성과

• 주소 실패율 7% → 0.2% 감소이다.
• 재작업 시간 9시간/주 → 30분/주 단축이다.
• 정제 매크로 배포 후 신규 직원 교육 시간 40% 절감이다.

5. VBA 자동화: 전체 시트 공백 일괄 제거

'모든 워크시트의 문자열 공백·제어 문자를 제거한다. Sub TrimAllSheets() Dim ws As Worksheet Dim rng As Range, c As Range Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets Set rng = ws.UsedRange For Each c In rng If VarType(c.Value) = vbString Then c.Value = WorksheetFunction.Trim( _ WorksheetFunction.Clean( _ Replace(c.Value, Chr(160), " "))) End If Next c Next ws Application.ScreenUpdating = True End Sub

코드는 들여쓰기와 주석 단위로 구분되어 초보자도 그대로 복사·붙여넣기할 수 있다.

6. 고급 팁: 공백 시각화와 오류 예방

Tip : Ctrl + H 대화상자에서 찾을 내용에 Alt+0160을 입력하면 ANSI 160 공백만 탐색할 수 있다. 입력할 때 숫자패드 사용이 필수이다.

• 조건부 서식에 =LEN(A2)<>LEN(TRIM(A2))을 적용해 공백이 남은 셀을 빨간색으로 표시하다.
• 피벗테이블을 만들기 전 데이터 > 텍스트 나누기 마법사를 통해 숨은 탭 문자를 점검하다.
• 함수 입력 중 수식 길이가 255자를 초과하면 TEXTJOIN으로 간소화해 성능을 높이다.
• Power BI 연결 시 Query Editor 단계에서 Trim·Clean 변환을 미리 넣어 모델 처리 시간을 단축하다.

FAQ

Q : TRIM 함수가 숫자 셀에서 효과가 없는 이유이다?

A : 숫자는 문자열이 아니므로 공백이 포함되지 않는다. 셀 서식이 텍스트로 강제 지정된 경우 VALUE 함수로 숫자로 변환한 뒤 TRIM을 사용할 필요가 없다.

Q : TRIM+CLEAN을 적용해도 공백이 남아 있는 이유이다?

A : ANSI 160 같은 비표준 공백이 추가로 존재하거나 UNICODE 8203(Zero-Width Space)처럼 보이지 않는 문자가 포함되어 있다. SUBSTITUTE로 해당 코드를 일반 공백으로 대체한 뒤 TRIM을 다시 적용한다.

Q : 한글·영문 혼합 데이터에서 TRIM 속도가 현저히 느려지는가?

A : 문자열 길이 대비 복잡도가 증가하지만 CPU 사용률이 5% 이내라 실무 지연은 미미하다. 대용량인 경우 Power Query 또는 VBA로 일괄 처리하면 속도가 개선된다.

Q : 공백이 제거된 뒤에도 VLOOKUP이 실패하는 이유이다?

A : 문자열 일치 여부 외에 데이터 형식이 다르기 때문이다. 원본은 텍스트, 참조표는 숫자이면 MATCH가 실패한다. VALUE 또는 TEXT 함수로 형식을 맞추면 해결된다.