산인 경우 하이픈이 있는 경우가 없어서,
A8셀의 주소를 경기도 가평군 북면 화악리 산 339에서 경기도 가평군 북면 적목리 산 1-1로 수정했습니다.
6. 필지구분(산과 일반) 구하기
일반이면 1이고, 산이면 2이므로 간단한데,
지번 데이터를 기준으로 생각하면 '산'이 있으면 2, 없으면 1이 됩니다.
따라서, 수식은
=if(left(c2,1)="산",2,1)
이 됩니다.
F2셀의 채우기 핸들을 더블 클릭하면 '산'인 경우 2로 잘 표시됩니다.
7. 본번과 부번을 4자리로 변경하기
가. 본번을 네 자리로 만들기
본번과 부번을 모두 4자리로 표시해야 합니다.
따라서, 앞에 '0"을 3개 붙인 후 뒤에서 4개를 가져와야 합니다.
그리고, 문제는 '산'이 있느냐 없느냐에 따라 다르고, 하이픈이 있느냐 없느냐에 따라 달라진다는 것입니다.
(1) '산'을 공백으로 만들기
그런데, '산'을 공백으로 대체하면 '산'에 따른 구분을 할 필요가 없습니다.
TRIM(SUBSTITUTE(C2,"산",""))로 해서 '산'을 빈칸으로 만들고, 산 다음의 앞쪽 공백도 제거합니다.
(2) 하이픈이 있는 경우 본번 구하기
다시 하이픈이 있는지 여부를 체크하기 위해
find("-",c2)라는 수식을 사용하는데,
하이픈이 없을 경우 에러가 발생하기 때문에
not(iserror( find("-",c2))) 또는 isnumber( find("-",c2))를 사용합니다.
=IF(NOT(ISERROR(FIND("-",C2))),
또는 IF(ISNUMBER(FIND("-",C2)),
를 조건으로 하고,
참이면 하이픈이 있는 것이므로 하이픈의 위치에서 1을 뺀 위치까지의 본번을 가져오는데 아래와 같은 수식을 사용합니다.
LEFT(TRIM(SUBSTITUTE(C2,"산","")),FIND("-",TRIM(SUBSTITUTE(C2,"산","")))-1)
왼쪽에서 가져오는 것이므로 Left 함수를 사용했고,
하이픈의 위치는 FIND("-",TRIM(SUBSTITUTE(C2,"산","")))이며, 여기서 1을 뺀 위치까지 본번을 가져와야 하므로 -1을 한 것입니다.
이때 FIND("-",C2)라고 하면 "산"이 있는 상태로 하이픈의 위치를 찾으므로
"산"이 있는 경우 본번이 잘못 구해지니 주의해야 합니다.
(3) 하이픈이 없는 경우 본번 구하기
이 때는 하이픈이 없으므로
"산"을 제거한 지번을 그대로 가져오면 됩니다.
TRIM(SUBSTITUTE(C2,"산",""))
(4) 본번 네 자리로 만들기
이제 위에서 구한 본번에 "000"을 결합한 후 오른쪽 4개를 가져오면 됩니다.
=RIGHT("000"&IF(ISNUMBER(FIND("-",C2)),LEFT(TRIM(SUBSTITUTE(C2,"산","")),FIND("-",TRIM(SUBSTITUTE(C2,"산","")))-1),TRIM(SUBSTITUTE(C2,"산",""))),4)
좀 복잡하지만 하나씩 쪼개서 만든 후 결합하면 됩니다.
'산'이 있는 경우에도 0238과 0001로 맞게 구해졌고, 지번이 1인 경우에도 0001로 잘 구해졌습니다.
1176의 경우는 숫자가 네 자리이므로 네 자리가 그대로 표시됩니다.
나. 부번을 네 자리로 만들기
마찬가지로 하이픈이 있는지 여부에 따라 다른데,
하이픈이 있다면 하이픈의 위치 + 1부터 숫자를 가져오고,
하이픈이 없다면 "0000"을 반환하도록 해야 합니다.
부번이므로 하이픈 이후 숫자만 가져오면 되므로 "산"이 있거나 없거나는 영향을 주지 않습니다.
하이픈이 있다면 하이픈의 위치 다음의 문자열을 가져와야 하므로
Left가 아니라 Mid함수를 사용해야 하며, 가져오는 개수는 4로 주면 됩니다.
MID(C2,FIND("-",C2)+1,4)
그리고, 하이픈이 없을 때는 ""을 반환하도록 하면 됩니다.
=IF(ISNUMBER(FIND("-",C2)),MID(C2,FIND("-",C2)+1,4),"")
그리고, 부번을 네 자리로 만들기 위해 "0000"(부번이 없을 수 있으므로 3이 아닌 4로 해야 함)과 연결하고, 오른쪽 4개를 가져오면 되므로 수식은
=RIGHT("0000"&IF(ISNUMBER(FIND("-",C2)),MID(C2,FIND("-",C2)+1,4),""),4)
이 됩니다.
8. PNU 코드 완성하기
가. Concat 함수 이용하기
이제 법정동코드와 필지구분, 본번, 부번을 연결하면 됩니다.
이때 & 연산자를 사용할 수도 있지만 번거로우므로
Concat 함수를 사용하는 것이 좋습니다.
나. 하나의 수식으로 PNU 만들기
지금까지는 법정동 코드를 만들고, 필지구분, 본번, 부번 등을 각각 구한 후 연결했는데,
위 수식을 하나로 연결하면 아래와 같이 됩니다.
=CONCAT(INDEX('법정동코드 전체자료.xlsx'!$A$1:$A$20555,MATCH(B2,'법정동코드 전체자료.xlsx'!$B$1:$B$20555,0)),IF(LEFT(C2,1)="산",2,1),RIGHT("000"&IF(ISNUMBER(FIND("-",C2)),LEFT(TRIM(SUBSTITUTE(C2,"산","")),FIND("-",TRIM(SUBSTITUTE(C2,"산","")))-1),TRIM(SUBSTITUTE(C2,"산",""))),4),RIGHT("0000"&IF(ISNUMBER(FIND("-",C2)),MID(C2,FIND("-",C2)+1,4),""),4))
엑셀 최신 버전에서는
아래와 같이 줄 바꾸기가 되는데, 구 버전에서는 안될 겁니다.
