TRIM · CLEAN · SUBSTITUTE로 텍스트 정리 끝! (공백·줄바꿈·특수문자 1분 컷)
보고서가 지저분해 보이는 진짜 이유는 ‘값’이 아니라 ‘공백/문자’입니다. TRIM·CLEAN·SUBSTITUTE 3총합으로 어디서 복붙해와도 깨끗한 라벨을 만들어요.
기본 개념과 차이
| 함수 | 역할 | 포인트 |
|---|---|---|
| TRIM | 앞뒤 공백 제거, 중복 공백 1칸으로 | 일반 공백(32)만 대상, NBSP(160)는 제외 |
| CLEAN | 인쇄 불가 문자 제거 | CR/LF 등 줄바꿈은 상황에 따라 남을 수 있음 |
| SUBSTITUTE | 문자열 치환 | 특정 문자/구분자 통일, NBSP→공백 변환 |
필살기 레시피 6가지
1) 웹에서 복붙한 공백·줄바꿈 한방 정리
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))2) 줄바꿈(개행) 제거 후 한 줄로
=TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(13)," "),CHAR(10)," "))3) 모든 공백 삭제(아이디·코드용)
=SUBSTITUTE(SUBSTITUTE(A2," ",""),CHAR(160),"")4) 구분자 통일(슬래시 → 하이픈)
=SUBSTITUTE(A2," / ","-")5) 괄호 안 텍스트 제거
=TRIM(SUBSTITUTE(A2, "(" & MID(A2,FIND("(",A2)+1, FIND(")",A2)-FIND("(",A2)-1) & ")", ""))6) 숫자만 추출(365)
=TEXTJOIN("",TRUE,IF(ISNUMBER(--MID(A2,SEQUENCE(LEN(A2)),1)),MID(A2,SEQUENCE(LEN(A2)),1),""))(구버전은 배열 수식으로 ROW($1:$99) 패턴 사용)
대량 클렌징 템플릿
LET 권장 동일 참조를 변수로 묶어 계산량을 줄입니다.
=LET(t, A2,
t1, SUBSTITUTE(t,CHAR(160)," "),
t2, SUBSTITUTE(SUBSTITUTE(t1,CHAR(13)," "),CHAR(10)," "),
TRIM(CLEAN(t2)))테이블(Table) 열에 수식을 한 번만 넣고 자동 채우면 유지보수도 쉬워집니다.
자주 하는 실수 & 성능 팁
- TRIM만 쓰면 NBSP가 남음 → 반드시
SUBSTITUTE(,CHAR(160)," ")먼저. - 줄바꿈 보정 누락 → CR(13), LF(10) 모두 치환.
- 치환 순서 → 치환 → TRIM → CLEAN 순으로 안전.
- 과도한 전체열 참조 → 정확 범위, 테이블 참조 사용.
요약 정리
| 목표 | 대표 공식 |
|---|---|
| NBSP/공백/줄바꿈 올인원 | TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) |
| 한 줄 만들기 | TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(13)," "),CHAR(10)," ")) |
| 모든 공백 삭제 | SUBSTITUTE(SUBSTITUTE(A2," ",""),CHAR(160),"") |
| 구분자 통일 | SUBSTITUTE(A2," / ","-") |
FAQ
REPLACE와 SUBSTITUTE의 차이는?
REPLACE는 위치/길이 기준으로 바꾸고, SUBSTITUTE는 특정 텍스트를 찾아 바꿉니다. 가변 길이 치환은 SUBSTITUTE가 적합합니다.
공백을 유지하면서 앞뒤만 제거하고 싶어요.
=TRIM(A2)는 내부 연속 공백을 한 칸으로도 줄입니다. 내부 공백을 유지하려면 앞뒤만 제거하는 별도 UDF 또는 패턴 처리가 필요합니다.