엑셀 문자열 처리 (MID함수, REPLACE함수, SUBSTITUTE 함수, 문자열 포함 여부 확인)

전체 목차
- 001. SUMIF 함수, SUMIFS 함수, COUNTIF 함수, COUNTIFS 함수
- 002. MATCH 함수, INDEX 함수
- 003. 엑셀 이동 단축키, 채우기 핸들, 수식 채우기 팁
- 004. DAVERAGE함수, DMAX함수, DMIN 함수
- 005. DCOUNT, DCOUNTA, DSUM, DGET, DPRODUCT, DVAR, DSTDEV 함수
- 006. 엑셀 절대주소 vs 상대주소 (절대주소 활용법)

- 007. 엑셀에서 배열 수식 사용하기 (1)

- 008. 엑셀에서 배열 수식 사용하기 (2)

- 009. 엑셀 문자열 처리(LEFT 함수, RIGHT 함수, LEN함수, FIND함수)

 

포스트 목차.

• 01. MID 함수
• 02. REPLACE 함수
• 03. SUBSTITUTE 함수
•  (1). 문자열 안에 특정 문자열 포함 여부 확인

본 포스트에서는 지난 포스트에 이어 엑셀에서 제공하는 문자열 처리 함수에 대해 다룬다. 

 

01. MID 함수

MID 함수에 대해 알아보자.

 

MID 함수는 문자열의 중간에 있는 텍스트를 원하는 만큼 잘라내어 반환한다. 함수의 인자는 다음과 같다.

 Text: 잘라내기 작업이 될 원본 문자열

 Start_num: 잘라내기 작업의 시작 위치

 Num_chars: 잘라낼 문자열의 길이

 

위의 예제에서는 길이가 13인 문자열 중에서, 7번째 위치에서 3개의 길이만큼을 잘라내어, "강남구"만 반환되는 것을 확인할 수 있다.

 

- MID 함수와 FIND함수를 응용하여 두 공백 사이의 문자를 추출하는 것이 가능하다.

문자열의 첫번째 공백의 위치를 구하는 수식은 다음과 같다.

=FIND(" ", A2)

*A2에 문자열이 있다고 가정한다.

 

 

문자열의 두번째 공백의 위치를 구하는 수식은 다음과 같다.

=FIND(" ",A2,D2+1)

위의 수식이 궁금하면, 지난 포스트를 참고하기 바란다.

 

위의 그림처럼, 첫번째 공백의 위치에서 1을 더하면 잘라낸 문자열의 시작위치가 나온다. 두번째 공백위치에서 첫번째 공백위치를 빼고 추가로 1을 더 빼면 잘라낼 길이가 나온다.

따라서 다음과 같은 수식을 사용하면 첫번째 공백과 두번째 공백 사이의 문자열을 구할 수 있다.

=MID(A2,FIND(" ",A2)+1,FIND(" ",A2,FIND(" ",A2)+1)-FIND(" ",A2)-1)

* 꼭 두 문자열이 같을 필요도 없고, 공백일 필요도 없다. 위의 예제는 다양하게 활용가능하다.

 

- MID 함수는 LEFT 함수와  RIGHT함수의 기능을 모두 수행할 수 있다.

LEFT 함수처럼 사용하려면, 두번째 인자인 시작위치(Start_num)를 1로 설정하면 된다.

위의 예에서는 A2에 들어있는 문자열에서 첫번째 위치부터 3글자를 잘라낸 결과를 의미한다.

 

 RIGHT 함수처럼 사용하려면, LEN함수와 함께 사용해야 한다. 

 다음른 RIGHT(A1, 3)과 같은 값을 반환하는 MID함수의 사용 예이다.

잘라낼 시작위치를 문자열의 길이 - 잘라낼 문자열의 길이 +1 로 설정하면 LEFT함수처럼 사용하는 것이 가능하다.

 

02. REPLACE 함수

REPLACE 함수는 문자열 내에서 대체할 텍스트의 위치를 결정한 후, 입력된 다른 문자열로 변경하는 역할을 한다. 인자는 다음과 같다.

 - Old_text: 원본 문자열

 - Start_num: 원본 문자열 내에서 바꿔야되는 문자열의 시작위치

 - Num_chars: 바꿔야되는 문자의 갯수 

 - New_text: 바꿔야 되는 문자열 대신 들어갈 문자열

 

위의 예에서는 바꿔야되는 문자열의 시작위치를 7로 설정하고, 바꿀 문자열의 길이를 4로 설정하여, "서대문구"가 바꿔야되는 대상이 된다. 여기에 New_text로 "강남구"를 설정하여, 서대문구 대신 강남구가 들어간 "서울특별시 강남구 도곡동"
이 반환된다.

 

03. SUBSTITUTE 함수

REPLACE 함수는 전체 문자열 내에서, 시작위치값과  길이로 변경할 문자열을 지정할 수 있었다. SUBSTITUTE 함수는 문자열의 위치와 길이 대신, 문자열 값을 이용하여 변경할 문자열을 지정할 수 있다. 인자는 다음과 같다.

 

- Text: 전체 문자열

- Old_text: 전체 문자열 내에서 찾아야 되는 문자열

- New_text: Old_text가 존재할 경우 대체될 신규 문자열

- Instance_num: Old_text가 여러개 존재할 때, 몇번 째 값을 바꿔야하는지를 지정한다. 생략시 가장 먼저 발견되는 문자열이 대상이 된다.

 

위의 예제에서는 Old_text "서대문구"의 값이 New_text "강남구"로 변경된다.

 

다음 예제를 통해 Instance_num의 활용에 대해 알아보자.

"토끼, 너구리, 토끼, 고양이, 호랑이, 토끼" 라는 문자열에는 토끼가 총 3번 등장한다. Substitute 함수를 이용하여, 토끼를 강아지로 바꾼다고 할 때, Instance_num의 값을 1에서 3까지 변경하면 위의 그림과 같은 결과를 얻을 수 있다.

 Instance_num의 값이 1인 경우 가장 먼저 등장하는 토끼가 강아지로 변경된다. 2인 경우 두번째로 등장하는 토끼가 강아지로 변경된다. 3인 경우 세번째로 등장하는 토끼가 강아지로 변경된다.

 

(1) 문자열 안에 특정 문자열 포함 여부 확인

Substitute 함수는 첫번째 인자(text)로 입력한 문자열 안에 두번째 인자(Old_text)가 없을 경우 아무일도 하지 않는다. 이것을 len함수와 함께 이용하여 문자열 포함여부를 확인하는 수식을 만들수 있다.

len함수는 이전 포스트에서 다뤘으니 궁금할 겨우 이전 포스트를 참고하기 바란다.

 

위의 예제는 C열에 입력되어 있는 문자열이 A열에 입력되어 있는 문자열에 포함되어 있는지를 D열에 표기한 예제이다.

D열의 수식을 다음과 같이 설정하면 문자열 포함여부를 구할 수 있다.

 

=LEN(A8)>LEN(SUBSTITUTE(A8,C8,""))

그냥 문자열의 길이와, 조사하려고 하는 문자열을 공백으로 변경한 문자열의 길이를 비교하는 논리연산을 통해 문자열 포함여부를 확인할 수 있다.

  - C열에 있는 문자열이 A열에 있는 문자열에 포함되어 있는 경우:

    SUBSTITUTE(A8, C8, "")에 의해 C8의 내용이 공백이되어 길이가 줄어들 것이다. 따라서 위의 논리식은 참이된다.

 - C열에 있는 문자열이  A열에 있는 문자열에 포함되어 있지 않은 경우:

   SUBSTITUTE는 아무런 일도 하지 않기 때문에 문자열의 길이가 그대로 유지되어 논리식은 거짓이 된다.

 

같이보면 좋은 글

• 엑셀 문자열 처리(LEFT 함수, RIGHT 함수, LEN함수, FIND함수)