엑셀 특정 글자, 문자 바꾸기 심화 (REPLACE, SUBSTITUTE)

 

 

엑셀에서 특정 문자를 다른 문자로 바꿔주는 함수인 REPLACE(특정 위치의 문자 변경)와 SUBSTITUTE(특정 문자를 찾아서 변경) 함수에 대해 알아보겠습니다. 특정 문자가 포함된 개수를 세서 원하는 위치의 문자만 바꾸는 방법까지 알아보겠습니다.

 

REPLACE

REPLACE 함수는 특정 위치의 문자를 강제로 바꾸는 함수입니다.

 

셀에 =REPLACE( 를 입력하면 아래 매개변수를 입력하라고 나옵니다.

 

엑셀 REPLACE

 

old_text : 작업이 필요한 대상 문자열(특정 셀 선택)

start_num : 변경을 시작할 위치의 숫자

num_chars : 몇자리를 바꿀지 정하는 숫자

new_text : 변경할 문자를 ""로 감싸준다.

 

엑셀 REPLACE

 

위 예제는 3번째 글자 1개(동)를 "대로"로 변경하는 예제입니다. 다른 동은 정상 변경되지만 글자 수가 다른 이태원동은 잘 못 변경된 것을 확인할 수 있습니다.

 

REPLACE 함수는 글자 수가 같은 상황에만 쓸 수 있습니다. 실제 데이터에서는 그런 경우를 찾기 힘들죠.

그래서 아래 함수를 씁니다.

 

---

 

SUBSTITUTE

SUBSTITUTE함수는 특정 문자를 찾아서 바꾸는 함수입니다.

 

셀에 =SUBSTITUTE( 를 입력하면 아래 매개변수를 입력하라고 나옵니다.

 

엑셀 SUBSTITUTE

 

text : 작업이 필요한 대상 문자열(특정 셀 선택)

old_text : 기존 문자(찾을 글자)

new_text : 변경할 문자

instance_num(선택) : 동일 문자가 있을 시 몇 번째 문자를 변경할 것인지 선택하는 옵션

 

엑셀 SUBSTITUTE

 

"동"을 찾아 "대로"로 변경하는 위 예제와 같이 REPLACE로 변경할 수 없는 이태원동도 정상 변경되었습니다.

 

다만 "동지동" 같이 찾을 문자가 여러 개이면서 어느 위치인지 특정할 수 없다면 문제가 생깁니다.

이런 문제의 해결을 위해서는 아래 예제를 참고하세요.

 

---

 

SUBSTITUTE 심화 응용

 

SUBSTITUTE로 찾을 문자가 여러 개이고, 위처럼 마지막 문자만 찾아 바꾸는 경우는 instance_num에 LEN함수를 응용해서 해결이 가능합니다.

 

엑셀 SUBSTITUTE 응용

 

함수를 위처럼 쓰면 해결됩니다.

 

간단히 설명하면

instance_num 외에는 동일하며, 전체 길이에서 찾을 문자를 제외한 문자의 길이만큼 빼버리면 찾을 문자열의 숫자가 나옵니다.

이와 같이 instance_num을 계산해서 입력한다면 원하는 위치의 문자를 찾아 바꿀 수 있습니다.