같은 글자가 중복되는 경우 마지막 글자의 위치 찾기(1) - Left, Max, Substitute, Len

1. 문제

같은 글자의 마지막 위치 찾기(문제).xlsx

0.01MB

 

 

"서울특별시 종로구 청운동"의 경우는 '동'이란 글자가 하나만 있기 때문에 간단한데,

"서울특별시 종로구 동숭동'의 경우는 '동'이란 글자가 두 번 나오므로 두 번째 '동'의 위치를 찾으려면 첫 번째 '동' 다음부터 '동'의 위치를 찾아야 합니다.

 

이런 식으로 '동'이 여러 번 나오는 경우 마지막 '동'의 위치를 찾아보겠습니다.

 

또한 '충청남도 천안시 동남구 동면 동산리'의 경우는 '동'이 세 번씩 나오지만 결국은 '리'가 중요한 요소이므로 '리'의 위치를 찾아야 합니다.

 

2. 해법

가. 동일한 글자 중 마지막 글자의 위치 구하기

(1) 한 가지 글자를 기준으로 할 경우

 

(가) 같은 글자수가 몇 개인지 알아내기

같은 글자수가 몇 개인지 알아내려면 전체의 길이에서 특정 글자를 공백으로 만든 다음  그 길이를 빼면 됩니다.

다시 말해 ' 서울특별시 종로구 청운동'에서 

전체 길이는 =len(a2)을 이용해 구하면 13이고,

 

=substitute(a2,"동","")하면 '동'이 없어지므로

 

그 길이를 =len( substitute(a2,"동",""))를 이용해서 12를 구한 후

 

빼면 1이 구해집니다.

 

이제 B2셀부터 D2셀까지 마우스로 끌어서 선택한 후 

D2셀의 채우기 핸들(아래 화면에서 빨간 색 네모 안의 네모 점)을 더블 클릭하면

 

'동'의 개수가 모두 구해집니다.

 

'대구광역시 달성군 화원읍 본리리'의 경우는 '동'이 없어서 D6셀의 값이 0인데 이것은 아래에서 다루겠습니다.

 

(나) 같은 글자 중 마지막 위치 구하기

위에서 구한 글자의 개수에 해당하는 '동'을 '@'로 바꾼 다음 @의 위치를 구하면 마지막 '동'의 위치가 됩니다.

https://lsw3210.tistory.com/481 참고

 

=FIND("@",SUBSTITUTE(A2,"동","@",D2))

 

위 수식에서 D2를 len(a2)- len( substitute(a2,"동",""))로 바꾸면

=FIND("@",SUBSTITUTE(A2,"동","@",LEN(A2)- LEN( SUBSTITUTE(A2,"동",""))))가 됩니다.

 

(다) 수식 복사

E2셀의 채우기 핸들을 더블 클릭해서 수식을 복사하면

13 등 위치가 구해지는데, 6열만 '동'이 없어서 D6셀의 값이 0이고, 따라서, #VALUE! 에러가 발생합니다.

 

(2) 두 가지 글자를 기준으로 할 경우

위에서 '리'가 여러 번 나오는 경우는 에러가 발생했기 때문에

이번에는 '동' 또는 '리'가 포함된 경우를 모두 처리할 수 있도록 해보겠습니다.

 

'동' 또는 '리'는 배열을 이용해 {"동","리"}로 입력할 수 있습니다.

따라서, E2셀의 수식에서 "동"을 {"동","리"}로 바꾸고 엔터키를 누릅니다.

=FIND("@",SUBSTITUTE(A2,{"동","리"},"@",LEN(A2)- LEN( SUBSTITUTE(A2,{"동","리"},""))))

 

그런데 결과가 13과 #VALUE! 에러로 배열로 값이 반환됩니다.

13은 마지막 '동'의 위치이고, #VALUE!는 마지막 '리'의 위치인데, '리'가 없어서 #VALUE!로 값이 반환되는 것입니다.

 

이제 #VALUE!를 ifError함수를 이용해 0으로 바꾸고, Max값을 구하면 '동' 또는 '리'의 마지막 위치가 됩니다.

=MAX(IFERROR(FIND("@",SUBSTITUTE(A2,{"동","리"},"@",LEN(A2)-LEN(SUBSTITUTE(A2,{"동","리"},"")))),0))

 

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

E6셀의 값도 17로 구해지며,

위치를 계산해 보면 글자수 14 + 공백 3 = 17 맞습니다.

 

 '충청남도 천안시 동남구 동면 동산리'의 경우도 글자수 15 + 공백 4 = 19로 맞습니다.

 

나. 마지막 글자의 위치까지 글자 가져오기

법정동명 뒤에 지번을 아래와 같이 넣어보겠습니다.

 

'동' 또는 '리'의 위치가 변하지 않습니다.

 

이제 A열에서 마지막 '동' 또는 '리'까지의 위치를 구하면 아래와 같습니다.

=LEFT(A2,MAX(IFERROR(FIND("@",SUBSTITUTE(A2,{"동","리"},"@",LEN(A2)-LEN(SUBSTITUTE(A2,{"동","리"},"")))),0)))

 

 

같은 글자의 마지막 위치 찾기(완성).xlsx

0.01MB