엑셀 / 함수 / SUBSTITUTE / 문자열 바꾸기

SUBSTITUTE는 문자열에서 특정 텍스트를 찾아 다른 텍스트로 바꾸는 함수입니다. occurrence 인수를 생략하면 일치하는 부분을 모두 바꾸고, 지정하면 해당 번째만 바꿉니다. 문자열의 고정 위치가 아니라 내용 자체를 바꾸고 싶을 때 사용합니다.

구문과 인수

=SUBSTITUTE(text,old_text,new_text,[instance_num])

text는 원본, old_text는 찾을 텍스트, new_text는 대체할 텍스트입니다. instance_num는 바꿀 번째를 나타내는 양의 정수이며 예를 들어 2이면 두 번째 일치 부분만 바꿉니다. 이 함수는 원본 셀을 지우지 않고 새 텍스트를 반환합니다.

구문과 지원 제품은 Microsoft의 SUBSTITUTE 함수 설명에서 확인할 수 있습니다.

모두 바꾸기와 특정 번째 바꾸기

=SUBSTITUTE("AB-CD-CD","CD","ZZ")
=SUBSTITUTE("AB-CD-CD","CD","ZZ",2)

SUBSTITUTE로 텍스트를 바꾸는 예제입니다. 위 수식은 각각 AB-ZZ-ZZ와 AB-CD-ZZ를 반환합니다. instance_num를 지정했는지에 따라 변경 범위가 달라집니다.

원본 텍스트에 일치하는 부분이 없으면 그대로 반환합니다. SUBSTITUTE는 대소문자를 구분하므로 "CD"를 찾는 조건으로 소문자 "cd"까지 바꾸지는 않습니다. 먼저 대소문자를 통일할 필요가 있는지도 자료의 의미에 맞춰 판단하세요.

불필요한 기호 제거 예제

=SUBSTITUTE(A2,"-","")

이 수식은 A2의 하이픈을 모두 제거합니다. 전화번호나 식별 코드를 가공할 때 사용할 수 있지만 결과는 텍스트입니다. 앞자리 0이 중요한 식별자를 숫자로 바꾸면 0이 사라질 수 있으므로 텍스트를 유지하는 편이 적합합니다.

여러 종류의 문자를 제거할 때는 SUBSTITUTE를 중첩할 수 있습니다. 다만 원래 단어 속의 의미 있는 기호까지 제거하지 않는지 대표 자료로 확인하세요. 공백 정리는 TRIM 함수, 위치와 문자 수를 정해 바꾸는 작업은 REPLACE 함수와 구분합니다.