엑셀 - 도로명주소 건물번호만 추출 또는 지번주소 지번만 추출

엑셀 작업을 하다 보면 find() 함수를 많이 사용하게 되는데, 앞에서부터 찾기 때문에 간혹 뒤에 있는 특정 문자를 찾을 때는 reverse 기능이 없는 것이 아쉬울 때가 있다. 이건 경우에 사용할 수 있는 함수가 substitute() 함수이다.

 

 

[엑셀 응용] 도로명주소에서 건물번호만, 지번주소에 지번만 찾기

아래와 같이 엑셀 시트에 도로명주소와 지번주소가 있을 때, 각 주소의 마지막에 있는 값(건물번호와 지번주소)을 추출해야 하는 경우, 

 

[엑셀 함수식]

# 건물번호
=TRIM(RIGHT(A2,LEN(A2)-FIND("T",SUBSTITUTE(A2," ","T",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))))

# 지번
=TRIM(RIGHT(B2,LEN(B2)-FIND("T",SUBSTITUTE(B2," ","T",LEN(B2)-LEN(SUBSTITUTE(B2," ",""))))))

원리를 설명하면,

• 주소에 공백 문자가 몇 개인지 찾는다. LEN(A2)-LEN(SUBSTITUTE(A2," ",""))
• SUBSTITUTE(A2," ","")는 전체 문자열 길이에서 공백을 제거한 문자열 길이를 빼는 수식이다.
• SUBSTITUTE(문자열, 찾을문자, 바꿀문자, [바꿀위치]) 함수에서 바꿀 위치는 찾는 문자가 여러 개 있는 경우 몇 번째를 대상으로 할지 결정하는 인수임(선택사항)
• 마지막 공백 문자(" ")를 임의의 문자("T")로 변경하고
• find() 함수로 임의의 문자("T") 위치를 찾는다.
• right() 함수로 건물번호 또는 지번을 추출하는데, 추출 지점은 전체 문자열 길이에서 "T"의 위치를 빼면 구할 수 있다.
• 마지막으로 주소 앞뒤로 공백이 있을 수 있기 때문에 trim() 함수로 공백을 제거한다.

 

 

[실행 결과]

엑셀 함수 적용 결과

C열, D열에 건물번호와 지번만 추출하였음