요약 — 같은 데이터라도 공백의 개수와 줄바꿈, 탭, 비가시 문자, 이종 대시와 따옴표가 섞이면 조회와 집계가 틀어집니다. 이 글은 Excel과 Google Sheets에서 TRIM CLEAN SUBSTITUTE만으로 원문을 안정적으로 정리해 표준 형태로 만드는 실전 패턴을 소개합니다. 샘플 CSV와 정규화 결과 CSV를 함께 제공하니 바로 내려받아 재현하실 수 있습니다.
파일 내려받기
- 샘플 원본 CSV: C3_cleanup_sample.csv
- 정규화 결과 CSV: C3_cleanup_result.csv
샘플 데이터 설명
샘플은 현장에서 자주 발생하는 혼합 공백과 특수문자를 의도적으로 포함합니다. 비가시 공백(CHAR160)과 탭, 줄바꿈, 하이픈의 다양한 유니코드 변형이 들어 있습니다.
| 컬럼 | 의미 | 예시 | 관찰 포인트 |
|---|---|---|---|
raw_name |
이름 원문 | lee jinwoo, park\tmi sun | 다중 공백, 탭, 비가시 공백 |
raw_phone |
연락처 원문 | 010–2345–6789, (010)5678-9012 | 서로 다른 대시와 괄호, 점 구분 |
raw_addr |
주소 원문 | Seoul Gangnam-gu Yeoksam-dong 123-45 |
줄바꿈과 비가시 공백 병존 |
raw_code |
코드 원문 | ab/12 3, zz 001 | 공백과 슬래시 혼용 |
raw_note |
비고 원문 | He said “ok” — confirm | 스마트 따옴표와 긴 대시 |
정규화 결과 구조
결과 CSV에는 다음과 같은 정리 열이 포함됩니다. 샘플과 비교해 공백과 특수문자가 한 가지 표준으로 정돈되어 있음을 확인하실 수 있습니다.
| 컬럼 | 의미 | 예시 | 핵심 규칙 |
|---|---|---|---|
name_clean |
정리된 이름 | Lee Jinwoo | 비가시 공백 치환 후 TRIM CLEAN 적용 |
phone_clean |
정리된 연락처 | 010-1234-5678 | 대시 변형 통일 후 불필요 기호 제거와 포맷 |
addr_clean |
정리된 주소 | Seoul Gangnam-gu Yeoksam-dong 123-45 | 줄바꿈과 탭 제거 후 단일 공백 |
code_clean |
정리된 코드 | AB-12-3 | 공백과 슬래시를 하이픈으로, 연속 하이픈 축소, 대문자 |
note_clean |
정리된 비고 | He said "ok" - confirm | 스마트 따옴표와 대시 통일 |
핵심 원리 요약
- 보이지 않는 공백을 먼저 눈에 보이는 공백으로 치환 — NBSP(CHAR160), 탭, 줄바꿈을 모두 빈 칸으로 바꿉니다.
- TRIM과 CLEAN으로 한 번 더 정리 — TRIM은 앞뒤와 중간의 중복 공백을, CLEAN은 비인쇄 문자를 제거합니다. Excel의 TRIM은 일반 공백만 처리하므로 NBSP는 SUBSTITUTE로 바꾸고 적용하는 것이 안전합니다.
- 하이픈과 따옴표를 표준 문자로 통일 — en dash, em dash, minus 기호를 통일하여 파싱 오류를 막습니다.
- 도메인 규칙을 반영한 후처리 — 코드의 구분자를 하이픈으로 고정하거나 연락처 자릿수를 맞춰 표시하는 등 업무 규칙을 반영합니다.
Excel 365 수식 레시피
원문이 A2에 있을 때 공백과 특수문자를 표준화하는 일반형입니다. 이 결과를 다른 후처리에 재사용하십시오.
=LET(
t, A2,
s1, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(t, CHAR(160), " "), CHAR(9), " "), CHAR(10), " "), CHAR(13), " "),
s2, TRIM(CLEAN(s1)),
s3, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(s2, "–", "-"), "—", "-"), "−", "-"),
s4, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(s3, "“", """"), "”", """"), "‘", "'"), "’", "'"),
s4
)연락처의 경우 특수문자를 지운 뒤 자릿수에 따라 포맷합니다. 010으로 시작하는 11자리 모바일 번호에 맞춘 예시입니다.
=LET(
t, A2,
u, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(CLEAN(SUBSTITUTE(t, CHAR(160), " "))), " ", ""), "–", "-"), "—", "-"), "−", "-"), "-", ""),
d, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(u, "(", ""), ")", ""), ".", ""), CHAR(10), ""), CHAR(13), ""),
IF(AND(LEN(d)=11, LEFT(d,3)="010"),
TEXTJOIN("-",, LEFT(d,3), MID(d,4,4), RIGHT(d,4)),
d)
)코드를 일관된 키로 바꾸는 예시입니다. 공백과 슬래시는 하이픈으로, 연속 하이픈은 하나로 축소하고 대문자로 고정합니다.
=LET(
t, A2,
u, TRIM(CLEAN(SUBSTITUTE(t, CHAR(160), " "))),
v, SUBSTITUTE(SUBSTITUTE(u, "/", "-"), " ", "-"),
w, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(v, "–", "-"), "—", "-"), "−", "-"),
x, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(w, "--", "-"), "--", "-"), "--", "-"),
UPPER(TRIM(x))
)Google Sheets 수식 레시피
Sheets에서는 TRIM이 중간 공백까지 정리하므로, NBSP만 일단 치환하고 TRIM CLEAN을 결합하십시오. 하이픈과 따옴표는 SUBSTITUTE로 통일합니다.
=LET(
t, A2,
s1, SUBSTITUTE(t, CHAR(160), " "),
s2, TRIM(CLEAN(s1)),
s3, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(s2, "–", "-"), "—", "-"), "−", "-"),
s4, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(s3, "“", """"), "”", """"), "‘", "'"), "’", "'"),
s4
)연락처의 기본 정리만 TRIM CLEAN SUBSTITUTE로 수행하고, 숫자만 남기는 단계는 선택적으로 REGEXREPLACE를 사용할 수 있습니다. 핵심은 앞 단계의 표준화가 되어 있어야 정규식도 안정적으로 동작한다는 점입니다.
=LET(
t, TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))),
u, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(t, "–", "-"), "—", "-"), "−", "-"), " ", ""),
d, REGEXREPLACE(u, "[^0-9]", ""),
IF(AND(LEN(d)=11, LEFT(d,3)="010"),
LEFT(d,3)&"-"&MID(d,4,4)&"-"&RIGHT(d,4),
d)
)코드의 구분자 표준화는 다음과 같이 구성합니다.
=LET(
t, TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))),
u, SUBSTITUTE(SUBSTITUTE(t, "/", "-"), " ", "-"),
v, SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(u, "–", "-"), "—", "-"), "−", "-"),
w, REGEXREPLACE(v, "-{2,}", "-"),
UPPER(w)
)검증 체크리스트
-- 이름 공백 검증. 두 칸 이상 공백이 남지 않는지 확인
=COUNTIF(정규화범위[name_clean], "* *") = 0
-- 연락처 자릿수 검증. 숫자만 남겼을 때 10자 또는 11자인지 확인
=COUNT(
FILTER(LEN(SUBSTITUTE(SUBSTITUTE(정규화범위[phone_clean], "-", ""), " ", "")),
(LEN(SUBSTITUTE(SUBSTITUTE(정규화범위[phone_clean], "-", ""), " ", ""))=10)
+ (LEN(SUBSTITUTE(SUBSTITUTE(정규화범위[phone_clean], "-", ""), " ", ""))=11)
) ) = ROWS(정규화범위[phone_clean])
-- 코드의 중복 하이픈 금지
=COUNTIF(정규화범위[code_clean], "*--*") = 0자주 묻는 질문
- CLEAN으로 NBSP가 안 지워지는데요 — NBSP는 ASCII 비인쇄 문자가 아니라서 CLEAN 대상이 아닙니다. 먼저
SUBSTITUTE(text, CHAR(160), " ")로 바꾸고 TRIM CLEAN을 적용하십시오. - 길이가 긴 줄바꿈이 남습니다 — 줄바꿈은 CHAR10, 캐리지리턴은 CHAR13입니다. 두 문자를 모두 공백으로 치환하십시오.
- 하이픈이 제각각입니다 — en dash, em dash, 수학용 minus를 모두
"-"로 통일하십시오. SUBSTITUTE를 세 번 겹치면 됩니다. - Sheets에서만 숫자 제거를 정규식으로 할 수 있나요 — 제목의 초점은 TRIM CLEAN SUBSTITUTE이지만, 실제 업무에서는
REGEXREPLACE(text,"[^0-9]","")가 가장 간단합니다. 전처리 표준화 후에 적용하면 더욱 안정적입니다.
정리
정규화의 본질은 입력의 다양성을 표준으로 수렴시키는 일입니다. 비가시 공백을 먼저 보이는 공백으로 치환하고 TRIM CLEAN으로 무게를 덜어낸 다음, SUBSTITUTE로 도메인별 표준 문자를 강제하면 파이프라인이 단단해집니다. 제공한 샘플과 결과 CSV를 내려받아 수식을 그대로 적용해 보십시오. 클릭 없이도 같은 규칙이 언제나 같은 결과를 내는 시트를 손에 넣으실 수 있습니다.