지번 형식 변환하기 1 (if, not, iserror, or, left, mid, find, substitute 등 함수 이용)

행정전산망의 지번 데이터는 엑셀로 변환시 문제점을 차단하기 위해서인지 본번과 부번을 연결할 때 ㅡ(대시)를 사용합니다. 예를 들어, 48-3처럼 하이픈이 아니라 48ㅡ3 식으로 대시로 되어 있습니다.

아래와 같은 데이터를 기준으로 설명합니다.

지번+형식+수정.xlsx

0.01MB

위 데이터는 두가지를 비교하기 위해 15번까지는 ㅡ, 16번부터는 -으로 지번을 연결했습니다.

1. 대시(ㅡ)를 하이픈(-) 으로 바꾸기

먼저 원시 데이터는 지우기 않기 위해 C2부터 C22까지 복사 후 E2에 붙여넣기를 합니다.
붙여 넣은 데이터를 보니 숫자만 이미 숫자 형식으로 표시되어, 오른쪽 정렬이 되었습니다.

홈 - 찾기 및 선택 - 바꾸기 메뉴를 누른 후, 찾을 내용에 대시 표시를 넣는데, 기호를 찾으려면 어려우므로 C2셀의 대시 부분을 끌어서 선택한 다음 Ctrl + C하고, 찾을 내용 칸에 커서를 넣고 Ctrl + V를 하시면 됩니다. 그리고, 바꿀 내용에 하이픈 표시를 한 후 왼쪽 아래 '모두 바꾸기' 버튼을 누르시기 바랍니다.

모두 바꾸기 버튼을 누르기 전에 E2에서 E22셀이 선택되어 있는지 확인하시기 바랍니다. 만약 한 셀만 선택되어 있는 상태에서 모두 바꾸기 버튼을 누르면 C열의 원시 데이터까지 바뀌게 되므로 주의하셔야 합니다. 만약 C열까지 바꿨다면 Ctrl + Z(이전으로) 키를 눌러 원상복구 후 다시 하면 됩니다.

그러면 '12개 항목이 바뀌었습니다'란 창이 나오는데, 확인 버튼을 누릅니다.
그리고, E8셀을 보면 '02월22일'이라고 되어 있고, E11셀을 보면 'Jun-67'이라고 되어 날짜 형식임을 알 수 있지만, 6월 67일은 이상하긴 하네요. 어쨌든 하이픈으로 바꾸면 하이픈 다음의 숫자(예 1,2 등)에 따라 하이픈 앞의 숫자(예를 들어 22, 67 등)와 관계없이 월일로 표시됨을 알 수 있습니다.

당연히 E18셀부터 E22셀까지는 이미 - 으로 되어 있으므로 변함이 없습니다.

2. 함수를 이용하여 대시를 하이픈으로 바꾸기
대시의 위치를 찾기 위한 find 함수, 조건에 따른 처리를 위한 if 함수, 몇 가지 조건을 연결하기 위한 or 함수, 왼쪽부터 문자를 가져오기 위한 left 함수, 특정 위치부터 몇 개의 문자를 가져오기 위한 mid 함수, 문자열 대체를 위한 substitute 함수, 에러인지 여부 판단을 위한 iserror 함수, false를 true로 변환하기 위한 not 함수가 필요하고, 문자열을 연결해주는 & 연산자가 합니다.

벌써부터 복잡한가요? 하나씩 해봅시다.

① 먼저 수식을 만들기 전에 논리부터 세우는 것이 중요합니다. 논리가 정리가 되면 그 다음 수식 만드는 것은 쉽습니다. 그러나, 모든 경우의 수를 고려해야 합니다. 만약 한 가지 경우라도 빠진다면 제대로 된 결과를 가져올 수 없습니다.

- 지번에 '산'이 있으면 그냥 표시하면 됩니다.
- 대시 다음의 숫자가 1에서 12사이일 경우만 날짜 형식 변환때문에 문제이고, 13이상이거나 대시 자체가 없을 경우는 문제가 없습니다.

- 그리고, 대시를 하이픈으로 바꾼 후 표시할 때도 대시가 없다면 하이픈을 표시할 필요가 없습니다. 다시 말해, 대시가 있을 경우만 하이픈을 넣어 앞 뒤 숫자를 연결해 표시하면 됩니다.

② 지번에 '산'이 있다면
if(left(c2,1)='산',참일 때 값, 거짓일 때 값)

③ 대시 다음의 숫자가 13이상인지 판단
먼저 대시의 위치를 찾아야 합니다. 대시의 위치를 찾기 위해서는 find("ㅡ",c2) 하면 되는데, 대시(ㅡ)가 없다면 에러가 나서 전체 수식이 에러가 발생하므로, not(iserror(find("ㅡ",c2))) 라고 써야 합니다. iserror함수는 에러이면 true를 반환해주는 함수이며, not은 false를 true로, 또는 그 반대로 바꿔주는 함수이므로 not(iserror(수식)) 하면 '수식이 에러가 아니면'이 됩니다.

대시 다음의 숫자는 mid(c2,find("ㅡ",c2)+1,4) 로 표시할 수 있는데,
대시 다음부터이므로 대시가 있는 위치인 find("ㅡ",c2)에 1을 더해야 하고, 가져올 개수는 4정도로 하면 됩니다.

대시 다음의 숫자가 13이상인지 ?
mid(c2,find("ㅡ",c2)+1,4)>=13 또는 mid(c2,find("ㅡ",c2)+1,4)>12 로 12보다 크다고 해도 됩니다.
조건에 맞으면 true를 반환합니다.

대시가 있을 경우, 대시 다음의 숫자가 12보다 큰지
if(not(iserror(find("ㅡ",c2))),mid(c2,find("ㅡ",c2)+1,4)>12,false)
위 수식을 해석하면 대시가 있을 경우 대시 다음의 숫자가 12보다 크면 true, 아니면 false를 반환해주는 것입니다.

④ 2번부터 4번까지 연결해서, "지번에 '산'이 있거나, 대시 다음의 숫자가 12보다 크다면"을 조건식으로 쓰려면 두 개의 조건이 '또는' 으로 연결되므로 or 함수를 써야 합니다. 따라서,
IF(OR(LEFT(C2,1)="산",IF(NOT(ISERROR(FIND("ㅡ",C2))),MID(C2,FIND("ㅡ",C2)+1,4)>12,FALSE)), 참일 때 값, 거짓일 때 값) 이 됩니다.

⑤ 4번 조건이 맞으면 그냥 대시를 하이픈으로 대체하면 됩니다.
substitute(c2,"ㅡ","-")

⑥ 4번 조건이 맞지 않으면 대시 이전의 문자와 대시 이후의 문자를 mid 함수를 이용하여 추출한 후 하이픈으로 연결하면 됩니다. 그런데, 이 경우도 대시가 있다면 대시 이전, 이후 문자를 연결하지만, 없다면 그냥 숫자를 문자 타입으로 표시해주면 됩니다.

대시가 있는지 여부는 위와 마찬가지로 if(not(iserror(find("ㅡ",c2))) 입니다.

대시 이전 문자는 처음부터 대시 있는 위치 -1 까지 입니다. 따라서, mid(c2,1,find("ㅡ",c2)-1) 이 됩니다.
대시 다음 문자는 대시 위치 +1 부터 4개를 가져오면 됩니다. 따라서, mid(c2, find("ㅡ",c2)+1,4) 가 됩니다.
위 2개를 하이픈으로 연결하면 mid(c2,1,find("ㅡ",c2)-1) & "-" & mid(c2, find("ㅡ",c2)+1,4) 가 되며, 중간에 있는 & 표시는 문자를 연결해주는 결합 연산자 입니다.

대시가 없다면 그냥 c2값을 가져오고 숫자로 인식되므로, mid 함수를 이용해서 문자로 변환합니다. 다시 말해 mid(c2,1,10) 라고 하면 됩니다. 4가 아니라 10인 이유는 하이픈으로 지번이 연결된 경우는 대시가 없기 때문에 이 경우에 해당되는데, 이 경우 본번과 부번이 모두 있으므로 길이를 넉넉하게 4가 아니라 10으로 준 것입니다.

6번을 모두 수식으로 연결하면,
IF(NOT(ISERROR(FIND("ㅡ",C2))),MID(C2,1,FIND("ㅡ",C2)-1)&"-"&MID(C2,FIND("ㅡ",C2)+1,4),MID(C2,1,10))
가 됩니다.

⑦ 위 모든 것을 수식으로 연결하면, F2의 수식은
=IF(OR(LEFT(C2,1)="산",IF(NOT(ISERROR(FIND("ㅡ",C2))),MID(C2,FIND("ㅡ",C2)+1,4)>12)),SUBSTITUTE(C2,"ㅡ","-"),IF(NOT(ISERROR(FIND("ㅡ",C2))),MID(C2,1,FIND("ㅡ",C2)-1)&"-"&MID(C2,FIND("ㅡ",C2)+1,4),MID(C2,1,10)))
가 됩니다.

복잡하죠? 그러나, 하나씩 연결해서 이렇게 길어진 것이므로 그렇게 어렵게 생각할 필요는 없습니다. 한 걸음씩 걷다 보면 어느 덧 정상에 도달해 있는 모습을 보게 되죠. 힘 내시기 바랍니다.

3. 대시 또는 하이픈 대응 및 본번, 부번 자릿수 지정하기
위의 것에 대시와 하이픈에 모두 대응하고, 본번 자릿수를 4, 부번 자릿수를 3으로 지정하고, "산"지번이 아닌 경우는 "일반"이라고 앞에 붙이도록 하려면 좀 더 복잡해지는데 아래와 같습니다.

큰 틀은 왼쪽 한 글자가 "산"인 경우에 (대시나 하이픈이 있는 경우, 없는 경우), "산"이 아닌 경우에 대시나 하이픈이 있는 경우, 없는 경우 네 가지로 나눠 값을 구하는 것입니다.
if 속에 if를 넣어야 하는데, "산"인 경우가 두 가지 경우가 있어 value_if_true 부분에 괄호를 넣어 If문을 value_if_true와 value_if_false를 구했고, "산"이 아닌 경우는 이미 value_if_false 부분이므로 조건을 줄 필요가 없으므로, 대시 또는 하이픈이 있는지 여부만 판단하는 if문만 넣으면 되므로 괄호를 넣지 않아도 됩니다.

if (산인 경우, (if( 대시나 하이픈이 있는지, value_if_true, value_if_false)), if( 대시나 하이픈이 있는지, value_if_true, value_if_false))

대시나 하이픈 둘중 하나만 있으면 되도록 or함수를 이용해 OR(NOT(ISERROR(FIND("ㅡ",C2))),NOT(ISERROR(FIND("-",C2)))) 로 수식을 사용했고,
대시나 하이픈이 없을 경우 #VALUE!에러가 나서 IFERROR(FIND("-",C2),0)를 사용해서 에러가 나면 0이 되도록 했습니다. 그러다 보니 더 복잡해졌네요.

G2의 수식은 아래와 같습니다.

=IF(LEFT(C2,1)="산",

(
IF(OR(NOT(ISERROR(FIND("ㅡ",C2))),NOT(ISERROR(FIND("-",C2)))),

"산"&RIGHT("0000"&VALUE(MID(C2,2,MAX(IFERROR(FIND("ㅡ",C2),0),IFERROR(FIND("-
",C2),0))-2)),4)
&"-"&RIGHT("0000"&VALUE(MID(C2,MAX(IFERROR(FIND("ㅡ",C2),0),IFERROR(FIND("-
",C2),0))+1,4)),4),

"산"&RIGHT("0000"&VALUE(MID(C2,2,4)),4)&"-0000")

),

IF(OR(NOT(ISERROR(FIND("ㅡ",C2))),NOT(ISERROR(FIND("-",C2)))),

"일반"&RIGHT("0000"&VALUE(MID(C2,1,MAX(IFERROR(FIND("ㅡ",C2),0),IFERROR(FIND("-
",C2),0))-1)),4)&"-
"&RIGHT("0000"&VALUE(MID(C2,MAX(IFERROR(FIND("ㅡ",C2),0),IFERROR(FIND("-
",C2),0))+1,4)),4),

"일반"RIGHT("0000"&VALUE(MID(C2,1,4)),4)&"-0000")

)

지번+형식+수정(완성)1.xlsx

0.01MB