주소 데이터를 다뤄본 사람은 압니다. 변환 자체보다 변환하기 전에 데이터를 사람이 볼 수 있는 상태로 만드는 일이 훨씬 오래 걸립니다. 수년간 쌓인 고객 주소에는 구주소와 도로명주소가 섞여 있고, 같은 아파트가 다섯 가지 표기로 들어 있고, 우편번호는 절반이 비어 있습니다. 이 글은 그 데이터를 정리하는 순서와 유형별 처리 기준을 정리합니다.
정제를 먼저 해야 하는 이유
정제되지 않은 데이터를 그대로 변환하면 오류가 어디서 났는지 알 수 없는 결과가 나옵니다. 원본이 이상해서 실패한 건지, 변환 과정에서 틀린 건지 구분이 안 되기 때문입니다.
순서를 지키면 문제의 원인이 단계별로 갈립니다. 정제 단계에서 걸러낸 것은 원본 문제, 변환 후에 나온 것은 표기 문제입니다. 이 구분만 되어도 수정 시간이 크게 줄어듭니다.
유형 1 — 구주소와 도로명주소 혼재
가장 흔한 문제입니다. 2011년 도로명주소 전면 시행 이전부터 데이터를 쌓아온 곳이라면 반드시 섞여 있습니다.
도로명: 서울시 강남구 테헤란로 152
구분 기준은 단순합니다. 「동」으로 끝나는 지명 뒤에 번지 숫자가 오면 구주소입니다. 「로」나 「길」 뒤에 숫자가 오면 도로명주소입니다. 엑셀에서 =IF(ISNUMBER(SEARCH("동 ",A2)),"구주소","확인") 정도로 1차 분류가 가능합니다.
구주소는 도로명주소로 먼저 변환해야 합니다. 둘의 구조적 차이는 도로명주소와 지번주소에서 다룹니다.
유형 2 — 상세주소 비정형
같은 아파트가 이렇게 들어 있습니다.
- 101동 1502호
- 101-1502
- 101동1502호
- 제101동 제1502호
- 1502호 (101동)
전부 같은 곳인데 문자열로는 전부 다릅니다. 자유 입력 필드라 사용자가 쓰고 싶은 대로 썼기 때문입니다.
여기서 완벽한 정규화를 노리면 끝이 없습니다. 현실적인 목표는 「동 숫자」와 「호 숫자」 두 개를 뽑아내는 것입니다. 두 숫자만 확보하면 Apt 101-1502 형식으로 통일할 수 있습니다. 괄호, 「제」, 공백 같은 나머지는 버려도 됩니다.
유형 3 — 중복과 유사 중복
완전히 같은 문자열은 엑셀 중복 제거로 걸러집니다. 문제는 사실상 같은데 문자열이 다른 경우입니다.
| 차이 유형 | 예시 | 처리 |
|---|---|---|
| 시·도 축약 | 서울시 / 서울특별시 / 서울 | 정식 명칭으로 통일 |
| 공백 위치 | 테헤란로152 / 테헤란로 152 | 숫자 앞에 공백 삽입 |
| 건물명 유무 | 주소만 / 주소+건물명 | 건물명은 별도 열로 분리 |
정제 후에 중복 제거를 해야 이런 것들이 함께 잡힙니다. 순서를 바꾸면 유사 중복이 그대로 남습니다.
유형 4 — 우편번호 불일치
우편번호 열이 있는 데이터라면 자리수부터 확인합니다.
- 6자리(135-080 형태) — 2015년에 폐지된 옛 번호입니다. 5자리로 다시 받아야 합니다.
- 앞자리 0 손실 — 엑셀이 숫자로 인식해
06236이6236이 된 경우입니다. 매우 흔합니다. - 주소와 불일치 — 이사 전 우편번호가 남아 있는 경우입니다.
앞자리 0 손실은 CSV로 주고받을 때 거의 반드시 발생합니다. 우편번호 열은 불러오기 단계에서 텍스트 서식으로 지정해야 합니다. 이미 잃어버렸다면 =TEXT(A2,"00000")으로 복구할 수 있습니다.
유형 5 — 주소가 아닌 값
주소 열에 주소가 아닌 것이 들어 있는 경우가 생각보다 많습니다. 「위와 같음」, 「직접 수령」, 「추후 연락」, 전화번호, 이메일, 그냥 공백. 이런 값은 변환기에 넣어봐야 의미가 없으므로 변환 대상에서 먼저 제외합니다.
판별 기준으로 「시·도 이름이 포함되어 있는가」를 쓰면 대부분 걸러집니다. 광역 단위 명칭 17개를 기준으로 검사하면 되고, 정식 표기는 행정구역 영문 표기표에서 확인할 수 있습니다.
정제 순서 정리
- 주소가 아닌 값 제외
- 공백·줄바꿈·특수문자 정리
- 시·도 명칭 통일
- 구주소를 도로명주소로 변환
- 상세주소 분리
- 중복 제거
- 우편번호 자리수 확인
- 영문 변환 실행
순서가 중요합니다. 중복 제거를 앞에 두면 표기만 다른 같은 주소가 살아남고, 상세주소 분리를 중복 제거 뒤에 하면 같은 세대가 여러 건으로 남습니다. 정리가 끝난 데이터는 대량 변환기에 넣고, 결과 검수는 엑셀 주소 대량 변환하기의 검수 기준을 따르면 됩니다.
자동화할 것과 사람이 봐야 할 것
정제 작업을 전부 자동화하려 하면 오히려 오래 걸립니다. 규칙이 명확한 것만 기계에 맡기고, 판단이 필요한 것은 사람이 보는 편이 빠릅니다.
| 구분 | 항목 |
|---|---|
| 자동화 가능 | 공백·줄바꿈 제거, 시·도 명칭 통일, 우편번호 자리수 맞추기, 완전 중복 제거 |
| 규칙으로 1차 분류 | 구주소 판별, 주소 아닌 값 걸러내기, 상세주소 숫자 추출 |
| 사람이 판단 | 같은 곳인데 표기가 다른 유사 중복, 추출 실패한 상세주소, 폐지된 주소 |
비율로 보면 전체의 90% 정도는 자동으로 처리되고, 나머지 10%가 사람 몫으로 남습니다. 이 10%를 줄이려고 규칙을 계속 추가하다 보면 예외의 예외가 생겨 유지보수가 어려워집니다. 남은 10%는 사람이 보는 게 정답이라고 정해두는 편이 낫습니다.
대신 사람이 볼 목록을 잘 만들어야 합니다. 원본 주소, 정제 결과, 걸린 이유 세 가지를 한 화면에 놓으면 한 건당 몇 초면 판단이 됩니다. 이유 없이 목록만 던지면 담당자가 매번 처음부터 다시 봐야 합니다.
자주 묻는 질문
구주소를 도로명주소로 한 번에 바꿀 수 있나요?
도로명주소 안내시스템에서 지번주소로 검색하면 대응하는 도로명주소를 확인할 수 있습니다. 건수가 많다면 정부에서 제공하는 주소 매칭 자료를 활용하는 방법도 있습니다.
우편번호 앞자리 0이 사라졌는데 복구되나요?
엑셀에서 =TEXT(A2,"00000") 수식으로 다섯 자리를 채울 수 있습니다. 다만 원래 네 자리였던 값과 구분이 안 되므로, 복구 후에는 주소와 대조해 검증하는 편이 안전합니다.
상세주소를 어디까지 정규화해야 하나요?
동 번호와 호 번호 두 개만 확보하면 충분합니다. 완벽한 정규화를 목표로 하면 예외가 끝없이 나오므로, 핵심 두 값만 뽑고 나머지는 원본을 보존하는 방식이 현실적입니다.
데이터가 몇 건부터 정제가 필요한가요?
건수보다 출처가 기준입니다. 사용자가 직접 입력한 데이터라면 50건이어도 표기가 흔들립니다. 반대로 시스템이 주소 API로 받아 저장한 데이터는 수천 건이어도 정제할 게 적습니다.
정제한 원본을 남겨둬야 하나요?
반드시 남겨야 합니다. 정제 과정에서 잘못 지운 값은 원본이 없으면 복구가 불가능합니다. 원본 열을 유지한 채 새 열에 정제 결과를 쌓는 방식을 권합니다.