엑셀 공백(스페이스) 일괄 삽입·제거: TRIM·SUBSTITUTE·CLEAN·Power Query로 끝내기
엑셀 공백 일괄 처리는 데이터 정리의 첫 단계입니다. 이 글에서는 TRIM/SUBSTITUTE/CLEAN 같은 기본 함수부터 TEXTSPLIT/TEXTJOIN, LET/SEQUENCE(365), 그리고 Power Query를 활용한 원본 일괄 변환까지 실무 패턴을 순서대로 제공합니다.
빠른 해결(Quick Fix)
① 공백 일괄 제거
- 선행·후행 + 중복 공백 1칸으로 축소:
=TRIM(A2) - 비분리공백(CHAR(160))까지 제거(웹 복사본에 흔함):
=TRIM(SUBSTITUTE(A2,CHAR(160)," ")) - 탭/줄바꿈까지 함께 제거(광범위):
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) - 모든 공백 제거(단 한 칸도 남기지 않음):
=SUBSTITUTE(SUBSTITUTE(A2,CHAR(160),"")," ","")
② 공백 일괄 삽입
- 구분자 뒤에 공백 하나 추가(예: 콤마 뒤):
=SUBSTITUTE(A2,",",", ") - N글자마다 공백 삽입(예: 3글자마다):
=LET(s,A2,n,3,k,SEQUENCE(ROUNDUP(LEN(s)/n,0)),TEXTJOIN(" ",,MID(s,(k-1)*n+1,n))) - 문자↔숫자 경계에 공백 삽입(365):
=LET(s,A2, L,LEN(s), ch,MID(s,SEQUENCE(L),1), flags, (ch>="0")*(ch<="9"), prev, DROP(flags,-1), cur, DROP(flags,1), cut, VSTACK(FALSE, (prev=1)*(cur=0) + (prev=0)*(cur=1)), TEXTJOIN("",,IF(cut," "&ch, ch)))의미: 숫자⇄문자 전환 경계에만 한 칸 삽입. (예:AB12C → AB 12 C)
③ 수식 없이 “원본을 바로” 바꾸려면
- 찾기/바꾸기(Ctrl+H) :
- 모든 공백 제거 → 찾을 내용:
- 구분자 뒤 한 칸 넣기 → 찾기:
,, 바꾸기:,(쉼표+스페이스).
- 모든 공백 제거 → 찾을 내용:
- Power Query (데이터 > 테이블/범위에서): 변환 > 형식 > 공백 제거(Trim), 정리(Clean) → 바꿔치기로 특정 문자 뒤에 공백 삽입 가능(고급: 사용자 지정 열).
왜 이런 문제가 생기나(개념·원리)
- 공백의 종류: 일반 스페이스(32), 비분리공백(160), 탭(9), 줄바꿈(10/13). TRIM은 32만 다루므로 웹/ERP 복사본은
CHAR(160)치환이 필요합니다. - 원본 보존 vs 직접 수정: 수식은 안전하고 되돌리기 쉽습니다. 원본을 직접 바꾸려면 Power Query나 찾기/바꾸기를 사용하세요.
- 표(Structured Reference)로 바꾸면
실무 예제
샘플 데이터
| 원본(A) | 설명 |
|---|---|
| Kim Seongjin | 선행/후행+중복 |
| 010 1234 5678 | CHAR(160) 포함 |
| AB12C | 문자/숫자 경계 |
| 2025-11-12,Seoul,Korea | 구분자 콤마 |
| ABCDEF | N글자마다 공백 |
1) 선행/후행/중복 공백 정리
B열: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")) → 결과: Kim Seongjin
2) 숫자/문자만 남기기(선택)
- 숫자만:
=TEXTJOIN("",,IFERROR(MID(A2,SEQUENCE(LEN(A2)),1)*1,"")) - 문자만:
=TEXTJOIN("",,IF(ISTEXT(MID(A2,SEQUENCE(LEN(A2)),1)),MID(A2,SEQUENCE(LEN(A2)),1),""))
3) N글자마다 공백 삽입
=LET(s,A2,n,3,k,SEQUENCE(ROUNDUP(LEN(s)/n,0)),
TEXTJOIN(" ",,MID(s,(k-1)*n+1,n)))예: ABCDEF → ABC DEF (n=3)
4) 구분자 뒤 공백 추가
=SUBSTITUTE(A2,",",", ") → 2025-11-12, Seoul, Korea
5) 문자↔숫자 경계 공백 삽입(365)
=LET(s,A2, L,LEN(s), ch,MID(s,SEQUENCE(L),1),
num,(ch>="0")*(ch<="9"),
br, VSTACK(FALSE, num<>DROP(num,1)),
TEXTJOIN("",,IF(br," "&ch,ch)))AB12C → AB 12 C
6) 표(Structured Reference) 적용
범위를 표(Ctrl+T)로 만들고 이름을 tbl, 열 이름이 [Raw]라면:
=TRIM(SUBSTITUTE(tbl[Raw],CHAR(160)," "))새 행이 추가되어도 자동 확장됩니다.
7) Power Query로 대량 클린업
- 데이터 → 테이블/범위에서 → Power Query 열기
- 변환 > 형식: 공백 제거(Trim), 정리(Clean) 적용
- 바꿔치기:
,를,로 치환하여 콤마 뒤에 공백 추가 - 닫기 & 로드로 시트에 반영(원본 연결 유지, 재새로고침 가능)
대체 방법/주의/체크리스트
- 웹/ERP 복사는 비분리공백(160)이 섞입니다 →
SUBSTITUTE(…,CHAR(160)," ")후 TRIM. - 전화번호·우편번호 등은 모든 공백을 제거해 비교/조인 정확도를 높이세요.
- 보고서 표시용은 구분자 뒤에 공백을 넣어 가독성을 확보하세요.
- 배치 변경이 필요하면 Power Query를 권장(재사용/되돌리기 쉬움).
- 수식 결과를 값으로 확정하려면 범위 복사 → 붙여넣기 값.
Troubleshooting
| 증상 | 원인 | 해결법 |
|---|---|---|
| TRIM인데 공백이 안 줄어듦 | CHAR(160) 비분리공백 | TRIM(SUBSTITUTE(A2,CHAR(160)," ")) |
| 탭/줄바꿈이 섞임 | 웹/CSV에서 유입 | TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))) |
| 쉼표 뒤 공백이 없어서 읽기 어려움 | 일괄 포맷 미적용 | SUBSTITUTE(A2,",",", ") |
| 원본을 직접 바꾸고 싶음 | 수식은 참조만 변경 | Ctrl+H 또는 Power Query로 변환 후 로드 |
| 대용량에서 느림 | 복잡한 배열 수식 다량 | Power Query/단계적 치환으로 분리 처리 |