주소를 PNU로 만들기(2) - '산' 다음에 공백이 없는 경우

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

 

TextBefore와 TextAfter 함수를 이용해 주소를 법정동과 지번으로 분리하기 모두

 

산과 지번 사이에 공백아 있는 경우만 다뤘는데, 이번에는 산과 지번이 공백 없이 붙어 있는 경우를 대상으로 살펴보겠습니다.

 

1. 주소 데이터

PNU만들기2.xlsx

0.01MB

 

'산'이 없는 경우는 동일하고, '산'이 있을 경우 산238과 같이 산과 본번사이에 공백이 없는 것만 다릅니다.

 

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

가. Substitute 함수 이용

'산'의 위치와 관계없이 공백의 개수를 기준으로 앞과 뒤를 나누면 됩니다.

 

(1) 법정동명 구하기

(가) Substitute 함수의 구문

SUBSTITUTE(text, old_text, new_text, [instance_num])로서

네 번째 인수가 instnce_num으로 몇 번째를 지정할 수 있습니다.

 

따라서, 공백의 개수를 instance_num에 넣으면 됩니다.

 

(나) 공백의 개수 구하기

공백의 개수는 문자열의 길이에서 공백을 없앤 다음의 문자열의 길이를 빼면 공백의 개수가 구해집니다.

 

(다) 법정동명 구하기

=TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",50),LEN(A2)-LEN(SUBSTITUTE(A2," ",""))),50))

 

 

(1) 마지막 공백을 공백 50개로 바꾸기

SUBSTITUTE(A2," ",REPT(" ",50),LEN(A2)-LEN(SUBSTITUTE(A2," ","")))는

A2셀 값에서 " "(공백 한 칸)을

REPT(" ",50) 공백 50개로 만드는데,

instance_num을 LEN(A2)-LEN(SUBSTITUTE(A2," ",""))로 A2셀의 문자열 길이에서 공백을 제거한 후의 문자열 길이를 뺀 공백의 개수에 해당하는 부분에 적용합니다.

 

따라서 위 수식의 결과는 

"강원특별자치도 춘천시 옥천동" 다음에 공백 50칸이 삽입되고  그다음에 지번 111-1이 오게 되므로

"강원특별자치도 춘천시 옥천동                                                  111-1"이 됩니다.

 

Microsoft 365라면 위와 같이 값을 알아볼 수식의 범위를 마우스로 끌어서 선택하면 그 위에 수식의 결괏값이 표시되는데,

Microsoft 365가 아니라면 F9키를 눌러야 수식의 결괏값을 알 수 있으며, 원 수식으로 돌아오기 위해서는 Esc키를 눌러야 합니다.

 

(2) 법정동명 분리하기

이제 위 값에서 왼쪽 50개를 가져오면 되는데,

Left함수의 결과는 오른쪽에 여전히 공백이 있으므로

 

좌우 공백을 제거하기 위해 Trim함수를 사용해야 합니다.

 

(라) 지번 구하기

지번은 세 번째 공백 이후 값을 가져오면 되므로

Left대신에 Mid함수를 사용하면 됩니다.

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

 

위와 같이 Left를 Mid로 바꿀 때

B2셀의 수식 입력줄에 커서를 넣고 Ctrl + A키를 눌러 수식을 모두 선택한 후, 마우스 오른쪽 버튼을 누르고(또는 Ctrl+C키) 수식을 복사한 후 Esc키를 누르고,

 

C2셀을 클릭하고 붙여 넣기를 한 다음

 

LEFT를 mid로 수정하고,

,50 다음에 ,50을 추가해서

50번째부터 문자열 50개를 가져오도록 하면 편리합니다.

 

나. TextBefore와 TextAfter 함수 이용

TextBefore와 TextAfter 함수가 가능하다면

공백의 개수를 기준으로 앞(TextBefore)과 뒤(TextAfter)의 문자열을 가져오면 되므로 매우 편리합니다.

 

(1) 법정동명 구하기

법정동명은 =TEXTBEFORE(A2," ",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))로 구하는데

 

세 번째 인수를 3으로 하지 않고 Len함수를 이용해 공백의 개수를 지정했는데, 공백의 개수가 주소에 따랄 다를 수 있기 때문입니다.

 

또한 Substitute와 LEFT함수를 사용했을 경우와는 달리 Trim함수를 사용할 필요도 없습니다.

 

(2) 지번 구하기

B2셀의 수식 입력줄에 커서를 넣고 모두 선택한 후 복사하고 Esc키를 누른 다음

C2셀을 클릭하고 붙여 넣은 다음

TextBefore를 TextAfter로만 수정하면 됩니다.

 

=TEXTAFTER(A2," ",LEN(A2)-LEN(SUBSTITUTE(A2," ","")))

 

Mid함수를 이용해서 몇 번째부터 몇 개를 가져오도록 할 필요도 없으므로 너무 편리합니다.

 

Microsoft 365가 아닌 사용자가 많을 것으로 생각되므로

Ctrl + Z키를 여러 번 눌러 Substitute 수식으로 돌려놓겠습니다.

 

3. 법정동명에 해당하는 법정동코드 구하기

법정동코드 전체자료.xlsx

0.53MB

 

 

위와 같이 법정동코드와 법정동명이 있는 파일과 연결해서 법정동명코드를 구하면 됩니다.

 

수식은 =INDEX('[법정동코드 전체자료.xlsx]Sheet1'!$A$2:$A$20555,MATCH(B2,'[법정동코드 전체자료.xlsx]Sheet1'!$B$2:$B$20555,0))입니다.

 

보다 자세한 설명은

주소를 PNU로 만들기(1-4) : 법정동명에 대한 법정동코드 찾기를 참고 바랍니다.

 

4. 필지구분 구하기

필지구분은 산이면 2, 아니면 1이기 때문에

=IF(LEFT(C2,1)="산",2,1)이 됩니다.

 

수식의 의미는 C2셀에서 왼쪽 한 글자를 가져와서 산이면 2, 아니면 1을 반환하는 것입니다.

 

5. 본번과 부번을 네 자리 숫자로 만들기

"산"이 있는지, 하이픈이 있는지에 따라 본번과 부번을 네 자리로 만드는 수식이 달라지는데,

 

가. 본번을 네 자리 숫자로 바꾸기

(1) 본번 가져오기

'산'이 있는지 여부에 따라 처리방법이 달라지므로 '산'을 없애는 것이 좋습니다.

SUBSTITUTE(C2, "산", "")

 

그러고 나서 하이픈이 있다면 (하이픈의 위치-1)까지 가져오고,

하이픈이 없다면 지번 전체를 가져오면 되므로 편리합니다.

 

따라서, 수식은

=IF(ISNUMBER(FIND("-",C2)),LEFT(SUBSTITUTE(C2,"산",""),FIND("-",SUBSTITUTE(C2,"산",""))-1),SUBSTITUTE(C2,"산",""))이 됩니다.

 

ISNUMBER(FIND("-",C2))는 C2셀에 하이픈이 있는지 체크하기 위한 것이고,

LEFT(SUBSTITUTE(C2,"산",""),FIND("-",SUBSTITUTE(C2,"산",""))-1)는 (하이픈의 위치-1)까지 글자를 가져오는 것이고,

SUBSTITUTE(C2,"산","")는 "산"을 없앤 지번을 가져오는 것입니다.

 

보다 자세한 설명은

주소를 PNU로 만들기(1-5) : 필지구분과 본번,부번 변환하기를 참고 바랍니다.

 

(2) 본번을 네 자리로 만들기

본번의 자릿수가 1개일 수도 있고 네 개일 수도 있으므로

앞에 "0"을 3개 붙인 후 오른쪽 4개를 가져오면 됩니다.

따라서 수식은

=RIGHT("000"&IF(ISNUMBER(FIND("-",C2)),LEFT(SUBSTITUTE(C2,"산",""),FIND("-",SUBSTITUTE(C2,"산",""))-1),SUBSTITUTE(C2,"산","")),4)가 됩니다.

 

나. 부번을 네 자리 숫자로 바꾸기

부번은 "산"이 있더라도 하이픈 위치만 참고하면 되므로

하이픈이 있으면 하이픈 다음부터 가져와서 "000"와 결합한 후 오른쪽 4개를 가져오고,

아니라면 "0000"을 반환하면 되므로 간단합니다.

 

수식은

=IF(ISNUMBER(FIND("-",C2)),RIGHT("000"&MID(C2,FIND("-",C2)+1,4),4),"0000")이 됩니다.

 

6. PNU 완성

이제 PNU는 법정동명코드부터 4개를 &로 결합하거나, D2셀부터 4개의 수식을 &로 연결하면 됩니다.

수식을 연결하면

=INDEX('[법정동코드 전체자료.xlsx]Sheet1'!$A$2:$A$20555,MATCH(B2,'[법정동코드 전체자료.xlsx]Sheet1'!$B$2:$B$20555,0))&IF(LEFT(C2,1)="산",2,1)&RIGHT("000"&IF(ISNUMBER(FIND("-",C2)),LEFT(SUBSTITUTE(C2,"산",""),FIND("-",SUBSTITUTE(C2,"산",""))-1),SUBSTITUTE(C2,"산","")),4)&IF(ISNUMBER(FIND("-",C2)),RIGHT("000"&MID(C2,FIND("-",C2)+1,4),4),"0000")입니다.

 

복잡해보여도 한 단계 한 단계의 수식을 결합한 것에 지나지 않습니다.

한 걸을 한 걸을 걸어가다 보면 정상에 도달하는 것과 같습니다.

PNU만들기2(완성).xlsx

0.41MB