주소 엑셀 정리 체크리스트: 변환 실패를 줄이는 7가지

1. 한 셀에는 주소 하나만

‘본점: 서울 ○○구 … / 지점: 부산 …’처럼 한 셀에 주소 두 개를 넣으면 어느 쪽도 제대로 찾지 못합니다. 주소가 여러 개라면 행을 나누세요. 이름, 연락처, 메모도 주소 셀이 아니라 옆 열에 두는 것이 원칙입니다.

2. 앞뒤 공백과 줄바꿈 지우기

다른 시스템에서 복사해 온 주소에는 눈에 보이지 않는 공백이나 줄바꿈이 섞여 있는 경우가 많습니다. 빈 열에 아래 수식을 넣고 아래로 채운 뒤, 결과를 ‘값만 붙여넣기’로 원래 열에 덮어쓰면 됩니다.

=TRIM(SUBSTITUTE(SUBSTITUTE(A1,CHAR(10)," "),CHAR(160)," "))

CHAR(10)은 셀 안 줄바꿈, CHAR(160)은 웹 페이지에서 복사할 때 자주 딸려 오는 ‘줄바꿈 없는 공백’입니다. TRIM은 앞뒤 공백을 지우고 중간의 연속 공백을 하나로 줄여 줍니다.

3. 층·호수와 상세주소는 떼어 두기

‘○○로 12, 3층 302호’처럼 상세주소가 붙어 있으면 인식이 흔들릴 수 있습니다. 변환에는 건물번호까지만 있으면 충분합니다. 쉼표 뒤에 상세주소가 붙는 형식이라면 다음 수식으로 앞부분만 꺼낼 수 있습니다.

=IFERROR(LEFT(A1,FIND(",",A1)-1),A1)

쉼표가 없는 행은 원래 값을 그대로 돌려줍니다. 상세주소 원본은 다른 열에 남겨 두면 나중에 다시 합칠 수 있습니다.

4. 괄호 안 참고항목은 지워도 됩니다

도로명주소 뒤에 붙는 ‘(역삼동, ○○아파트)’ 같은 괄호는 참고항목입니다. 법정동과 건물 이름을 보여 주는 용도라 주소를 찾는 데 꼭 필요하지 않고, 오타가 섞여 있으면 오히려 방해가 됩니다. 결과가 이상하게 나오는 행이 있다면 괄호를 지우고 다시 시도해 보세요.

5. 시·도와 시·군·구를 빠뜨리지 않기

‘중앙로 12’처럼 도로명만 적으면 같은 이름의 도로가 전국 여러 곳에 있어서 엉뚱한 지역이 잡힐 수 있습니다. 동 이름도 마찬가지입니다. 예를 들어 ‘신사동’이라는 법정동은 서울 강남구와 은평구에 모두 있고, 관악구에는 같은 이름의 행정동이 있습니다. 최소한 시·군·구까지는 붙여 두세요.

‘서울시’, ‘서울특별시’, ‘서울’처럼 표기가 섞여 있다면 하나로 통일해 두는 편이 결과를 검토하기에도 편합니다. SUBSTITUTE 함수나 ‘찾기 및 바꾸기(Ctrl+H)’를 쓰면 한 번에 바꿀 수 있습니다.

6. 병합된 셀과 빈 행 정리하기

보고서용으로 꾸민 엑셀에는 셀 병합, 소계 행, 중간 제목 행이 섞여 있는 경우가 많습니다. 병합된 셀은 첫 칸에만 값이 들어 있어서 나머지 행은 빈칸으로 읽힙니다. 변환 전에 병합을 해제하고, 주소가 아닌 행(소계, 구분선)은 지우거나 별도 시트로 옮기세요.

7. 지번과 도로명이 섞여 있어도 괜찮습니다

‘역삼동 858’ 같은 지번 주소와 ‘테헤란로 152’ 같은 도로명주소가 한 열에 섞여 있어도 그대로 변환할 수 있습니다. 굳이 한쪽으로 통일하려고 시간을 쓸 필요는 없습니다. 다만 지번 주소라면 번지까지 적혀 있어야 정확한 위치를 찾을 수 있습니다.

변환 후 검토 요령

• 결과 파일에서 ‘검색 실패’로 필터링해 해당 행만 고친 뒤 다시 변환합니다.
• 법정동 열을 기준으로 정렬해 보면, 다른 시·도의 동 이름이 섞여 있는지 눈으로 빠르게 확인할 수 있습니다.
• 피벗 테이블로 행정동별 건수를 세면 지역별 고객 분포를 바로 볼 수 있습니다.

엑셀 파일 준비와 업로드 방법은 엑셀 변환 가이드에 자세히 정리해 두었습니다.