엑셀 전문가가 말해주는 SUBSTITUTE 함수로 텍스트 가공하기

엑셀을 활용하다 보면 특정 텍스트나 문자 일부를 다른 값으로 바꿔야 하는 경우가 자주 발생합니다. 예를 들어, 전화번호의 하이픈을 제거하거나, 특정 단어를 다른 단어로 바꾸는 등의 작업이 대표적입니다.

이럴 때 유용하게 사용할 수 있는 함수가 바로 SUBSTITUTE 함수입니다. SUBSTITUTE 함수는 텍스트 내 특정 문자열을 원하는 값으로 바꾸는 함수로, 대량의 텍스트 데이터 처리 시 업무 속도를 획기적으로 높여줍니다.

 

1. SUBSTITUTE 함수란?

SUBSTITUTE 함수는 텍스트 문자열 내 특정 문자를 찾아 다른 문자로 바꿔주는 함수입니다. 특히, 정확하게 일치하는 텍스트만 바꾸기 때문에 깔끔한 텍스트 정리 작업에 매우 적합합니다.

 

기본 형식

복사

편집

=SUBSTITUTE(원본텍스트, 바꿀문자, 새문자, [대체순서])

원본텍스트: 텍스트가 들어 있는 셀 또는 텍스트 문자열

바꿀문자: 교체하고자 하는 기존 문자

새문자: 바꾼 후의 새 문자

 

[대체순서]: 바꾸고 싶은 항목의 위치 (선택 사항)

 

2. SUBSTITUTE 함수 실전 예시

 

 

2.1 전화번호에서 하이픈 제거하기

전화번호가 010-1234-5678처럼 하이픈이 포함된 경우, 하이픈을 제거하고 숫자만 남기고 싶을 때 아래와 같이 활용합니다.

arduino

복사

편집

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

 

위 함수는 A2 셀에 있는 하이픈을 모두 제거하여 01012345678로 만들어 줍니다.

 

2.2 오타 수정 및 특정 단어 일괄 변경

예를 들어, 상품명에 삼성ㅂ이라는 오타가 여러 개 있다면 이를 삼성으로 일괄 변경할 수 있습니다.

arduino

복사

편집

=SUBSTITUTE(A2, "삼성ㅂ", "삼성")

 

이렇게 하면 사람이 일일이 고치지 않아도 엑셀이 자동으로 수정해줍니다.

 

2.3 특정 위치의 텍스트만 바꾸기

SUBSTITUTE 함수는 동일한 단어가 여러 번 나올 경우, 몇 번째 단어만 바꿀지 선택할 수 있습니다.

 

예시 텍스트: 사과, 사과, 바나나

두 번째 사과만 바꾸고 싶다면,

arduino

복사

편집

=SUBSTITUTE("사과, 사과, 바나나", "사과", "딸기", 2)

결과는 사과, 딸기, 바나나가 됩니다.

 

2.4 텍스트 정제 자동화에 활용

고객 입력값 중에서 공백이나 특수문자가 섞여 있는 경우, 이를 제거하는 데도 사용할 수 있습니다.

arduino

복사

편집

=SUBSTITUTE(A2, " ", "")

=SUBSTITUTE(A2, ".", "")

 

여러 SUBSTITUTE 함수를 중첩하면 여러 개의 문자를 한 번에 정리할 수 있습니다.

 

3. SUBSTITUTE vs REPLACE

 

 

SUBSTITUTE 함수와 비슷한 REPLACE 함수도 있지만, 차이가 있습니다.

 

구분 SUBSTITUTE 함수 REPLACE 함수

용도 특정 문자열을 바꾸는 데 사용 위치 기준으로 텍스트를 대체

대상 특정 단어 또는 문자 지정한 위치의 문자

활용 예시 오타 수정, 단어 변경 문자열 일부 잘라서 다른 문자로 바꾸기

 

따라서 단어를 정확하게 바꾸고 싶다면 SUBSTITUTE 함수, 위치 기준으로 편집하고 싶다면 REPLACE 함수가 적합합니다.

 

4. 마무리하며

SUBSTITUTE 함수는 텍스트 편집을 수동으로 하지 않아도 되게 만들어주는 매우 강력한 함수입니다. 특히 고객 데이터, 주소, 상품명, 연락처 등의 텍스트 정리가 필요할 때 그 진가를 발휘합니다.

함수 하나로 업무 자동화, 오타 수정, 포맷 통일까지 할 수 있다는 점에서

엑셀 업무 효율을 크게 높일 수 있는 핵심 도구로 추천드립니다.

 

2025.04.25 - [엑셀(EXCEL)] - 엑셀 전문가가 말해주는 FIND, SEARCH 함수로 업무 자동화하기