주소를 PNU로 만들기(1-3) : 주소를 법정동명과 지번으로 분리하기

4. 주소를 법정동과 지번으로 분리하기

 

가. 주소의 형태

PNU만들기.xlsx

0.01MB

 

 

아래와 같이 시도, 시군구, 읍면동, 리로 구성된 주소가 있을 때

법정동과 지번을 구분해야 하는데

한 칸 공백의 개수가 3개인 경우도 있고, 4개, 5개인 경우도 있습니다.

 

나. 주소를 법정동과 지번으로 나누는 논리

필지 구분이 '산'인지, '일반'인지에 따라 주소 나누는 것을 달리해야 합니다.

그런데, '산'도 한 글자인 경우도 있고, '산청군'과 같이 한글자 이상에 포함된 경우도 있어서 복잡해집니다.

 

그렇다면 공백의 개수를 구한 다음 (총 공백의 개수-1)의 위치에 '산'이 있는지 여부를 먼저 알아내고

만약 '산'이라면 그 위치부터 맨 끝까지가 지번이 되고,

'산'이 아니라면 '총 공백의 개수' 위치 다음부터가 지번이 됩니다.

 

그리고, 법정동은 지번이 있는 위치에서 -2까지가 됩니다.

 

다. 지번 분리해 내기

(1) 총 공백의 개수 구하기

총 공백의 개수는 문자열의 길이에서 한 칸 공백을 빈 칸으로 만든 문자열의 길이를 빼서 구할 수 있습니다.

수식은 =LEN(A2)-LEN(SUBSTITUTE(A2," ",""))이 됩니다.

 

SUBSTITUTE(A2," ","")가 A2셀에서 한 칸 공백을 빈칸으로 바꾸는 구문이고,

Len함수는 문자열의 길이를 구하는 함수입니다.

 

(2) 총 공백의 개수 -1의 위치에 있는 문자가 '산'인지 파악하기

여러 가지 방법이 있을 수 있지만 아래와 같은 방법으로 해보겠습니다.

 

먼저 특정 위치의 문자를 찾는 것은

특정 기호 사이의 문자 찾기(2)을 참고하면 되는데,

 

이 글을 참고해서 총 공백에서 1을 뺀 위치의 값을 구하려면

수식은 

=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",50)),(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1)*50,50))

이 됩니다.

 

위 수식에서

SUBSTITUTE(A2," ",REPT(" ",50))는 한 칸 공백을 50개의 공백으로 만들어서 주소에서 한 칸 공백 사이의 문자열을 구하기 좋게 하기 위한 것이고,

 

(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1)는 총 공백의 개수에서 1을 빼는 것으로 A2셀의 경우는 3-1=2가 됩니다.

 

이것이 Mid함수의 두번째 인수로 들어가고,  세번째 인수가 50이기 때문에  두번째 공백부터 50개의 문자열을 가져오는 것입니다.

 

따라서, Mid함수를 이용한 수식의 값은 '옥천동'의 좌우에 공백이 있게 되며,

 

좌우공백을 제거하기 위해 Trim함수를 사용해서 '옥천동'을 구하는 것입니다.

 

(3) 지번 구하기

이제  C2셀의 채우기 핸들을 더블 클릭하면 동,리,가가 구해지는데, '산'인 경우만 찾아야 하므로

 

=if(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",50)),(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1)*50,50))="산",

이라고 조건을 주고, '참일 때 값'은 그 위치이후의 값을 가져오면 되고,

아니라면 총 공백의 개수 다음의 문자열을 가져오면 됩니다.

 

따라서, 수식은 아래와 같이 됩니다.

=IF(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",50)),(LEN(A2)-LEN(SUBSTITUTE(A2," ",""))-1)*50,50))="산",

MID(A2,FIND("산 ",A2),10),

TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",50)),(LEN(A2)-LEN(SUBSTITUTE(A2," ","")))*50,50))

)

 

MID(A2,FIND("산 ",A2),10)는 "산 "의 위치를 찾아서("산청군"이 포함되면 안되므로 "산"이 아니고, 한 칸 공백을 추가한 것임) 그 위치부터 넉넉하게 10개의 문자열을 가져오는 것인데, 10개를 가져오더라도 글자가 있는 위치까지만 가져오므로 Trim을 사용하지 않아도 됩니다.

 

TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",50)),(LEN(A2)-LEN(SUBSTITUTE(A2," ","")))*50,50))은 LEN(A2)-LEN(SUBSTITUTE(A2," ","")) 다음의 -1을 빼서 총 공백의 개수 다음부터 문자열을 가져오므로 '111-1'이 구해지는 것입니다.

 

이제 C2셀의 채우기 핸들을 더블 클릭하면 원하는대로 법정동을 제외한 지번이 구해졌습니다.

라. 법정동 분리해 내기

이제 지번의 위치를 찾아서 그 위치에서 -2까지의 문자열을 가져오면 됩니다.

 

따라서, 수식은

=LEFT(A2,FIND(C2,A2)-2)이 됩니다.

 

지번을 제외한 옥천동까지 법정동을 잘 가져왔습니다.

 

이제 B2셀의 채우기 핸들을 더블 클릭하면

모두 주소가 법정동과 지번으로 구분됩니다.

PNU만들기(주소분리).xlsx

0.01MB