[엑셀] 오른쪽에서 특정 문자까지 문자열 자르기

2016/12/29 - [엑셀] - [엑셀] 왼쪽에서 특정 문자까지 문자열 자르기에 이어서

오른쪽에서 특정 문자까지, 특히 그 특정 문자가 여러 개일 경우에도 오류 없이 추출하는 방법을 알아 보자.

(만일 한 줄에 특정 문자가 한 번만 등장한다면, 간단히 위의 링크에 나온 방법을 활용하면 되겠다.)

결론부터 말하자면, 아래의 수식을 복사하여 사용하면 된다.

=RIGHT(셀,LEN(셀)-FIND("임의문자열",SUBSTITUTE(셀,"찾을문자열","임의문자열",LEN(셀)-LEN(SUBSTITUTE(셀,"찾을문자열","")))))

여기서 변경해 주어야 하는 것은 [셀]과 [찾을문자열] 두 가지로,

메모장 등에 복사한 후 Ctrl + H 등으로 찾아 바꾸면 쉽게 바꿀 수 있다.

전략은 이렇다. 

식별해야 할 문자가 여러 개이므로

가장 마지막(오른쪽)에 있는 문자만 다른 문자로 잠시 바꾸어 두자는 것이다.

예를 들어 010-1234-1234 를 010-1234ㅁ1234 로 바꿀 수 있다면

이후에는 "ㅁ"이라는 문자만 찾아서 그 오른쪽 문자열을 추출하면 된다.

이를 위해 SUBSTITUTE() 함수가 사용된다. SUBSTITUTE()는 문자열을 치환하는 함수로서

SUBSTITUTE(셀, 찾을문자열, 바꿀문자열, 순서지정) 과 같이 사용된다.

중요한 것은 네 번째 요소인 '순서지정'으로, 예를 들자면

위와 같이 순서를 2로 지정할 경우 2번째에 있는 "-" 만을 찾아서 "ㅁ"으로 바꾼다.

하지만 우리가 찾을 문자가 문자열 내에 몇 개나 등장하는지 모른다면 순서를 숫자로 지정해 줄 수는 없는 노릇이다.

아무래도 이 기능이 가장 유용한 곳은 '오른쪽 어절 추출' 처럼 오른쪽에서 첫 번째 빈칸까지를 찾는 경우인데

만약 다음과 같은 자료에서 가장 오른쪽에 있는 어절들을 떼어내고 싶다면 몇 번째 빈칸을 바꾸라고 이야기할 것인가?

그러므로 또 하나의 아이디어가 필요한데,

바꿀 문자열을 일단 없애고 원래 문자열과의 길이 차이를 보는 것이다.

즉,

생각해 보면 C열에 있는 문자열 길이의 차이가 곧 '몇 번째 있는 빈칸을 바꾸어야 하는지'에 해당하는 숫자라는 것을 알 수 있다.

그러므로 그 부분만 "ㅁ"으로 바꾸어 보자. 당연히, 이미 있는 문자열에 중복되지만 않는다면 "ㅁ" 외에 다른 문자를 사용해도 무방하다. 

알아보기 쉽도록 SUBSTITUTE()의 네 번째 요소인 '순서지정' 만 별도의 행에 적었다. 

의도대로 잘 바뀌는 것을 볼 수 있다.

이제 앞서 나왔던 오른쪽 문자열 추출과 덧붙이면 된다.

FIND()에 들어가는 '셀' 부분만 우리가 앞서 ㅁ으로 바꾼 가상의 셀로 지정해 주었다.

ㅁ은 이해를 돕기 위해 한 글자로 지정한 것이므로, 중복 우려가 있다면 원하는 문자열로 지정해 주어도 무방하다. 예를 들어

와 같이 하여도 무방하다.

혹시 마지막 어절을 추출하는 게 아니라 지우고 싶다면?

SUBSTITUTE(셀, 방금 찾은 부분, "") 와 같이 적어 주면 되겠다.

다만 이렇게 할 경우 정확히 아까 찾은 부분(Kennedy)만 삭제하므로

John F. 가 아니라 John F. 까지 빈칸이 하나 더 남게 된다.

이를 없애려면 위 '방금 찾은 부분'의 RIGHT()함수에 한 칸을 더해주면(+1) 되겠다.

물론 찾을 문자가 3글자라면 +1 대신 +3을 해 주어야 한다.

여기서는 1문자의 빈칸을 찾았으므로 +1을 해 주었다.

---

혹시 엑셀이 아니라 구글 스프레드시트를 사용할 수 있는 환경이라면

REGEXREPLACE() 함수를 사용하는 것이 더 나은 방법이겠다.