엑셀 SUBSTITUTE 함수 - 엑셀에서 특정 문자 한 번에 바꾸기

SUBSTITUTE 함수란?

엑셀의 SUBSTITUTE 함수는 문자열 안에 있는 특정 문자나 단어를 다른 문자로 바꾸는 함수입니다.

문장을 수정하거나, 특정 기호를 제거하거나, 데이터 형식을 변경할 때 매우 자주 사용하는 함수입니다.

예를 들어,

• 하이픈(-) 제거
• 쉼표(,)를 공백으로 변경
• 회사명을 다른 이름으로 변경
• 특정 단어만 교체

등의 작업을 쉽게 처리할 수 있습니다.

---

SUBSTITUTE 함수 기본 문법

=SUBSTITUTE(텍스트, 기존문자, 새문자)

 

또는

=SUBSTITUTE(텍스트, 기존문자, 새문자, 바꿀순서)

인수 설명

• 텍스트 : 변경할 문자열
• 기존문자 : 찾을 문자
• 새문자 : 변경할 문자
• 바꿀순서(선택) : 몇 번째만 변경할지 지정

---

가장 쉬운 예제

예제 1. 하이픈 제거

=SUBSTITUTE("010-1234-5678","-","")

 

결과

01012345678

전화번호에서 하이픈을 제거할 수 있습니다.

---

예제 2. 회사명 변경

=SUBSTITUTE("ABC전자","ABC","가벼운")

 

결과

가벼운전자

특정 단어만 원하는 이름으로 변경할 수 있습니다.

---

예제 3. 쉼표를 공백으로 변경

=SUBSTITUTE("사과,배,포도",","," ")

 

결과

사과 배 포도

CSV 형태의 데이터를 보기 좋게 만들 때 자주 사용됩니다.

---

네 번째 인수 사용하기

네 번째 인수를 사용하면 원하는 위치만 변경할 수 있습니다.

 

예제

=SUBSTITUTE("A-A-A","-","/",2)

결과

A-A/A

두 번째 "-"만 "/"로 변경됩니다.

---

실무 활용 예제

1. 주민번호 기호 제거

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

---

2. 전화번호 정리

=SUBSTITUTE(B2,"-","")

---

3. 이메일 수정

=SUBSTITUTE(C2,"gmail","naver")

---

4. 파일명 변경

=SUBSTITUTE(D2," ","_")

공백을 언더바(_)로 변경할 수 있습니다.

---

SUBSTITUTE와 REPLACE 함수 차이

 

함수	기준사용	목적
SUBSTITUTE	특정 문자 찾기	같은 단어를 모두 변경
REPLACE	위치 번호	지정한 위치 문자 변경

 

예를 들어,

A-B-C

 

에서 "-"를 모두 바꾸려면

=SUBSTITUTE(A1,"-","/")

 

문자의 위치를 기준으로 변경하려면

=REPLACE(A1,2,1,"/")

을 사용합니다.

---

자주 사용하는 조합 함수

SUBSTITUTE + TRIM

공백 정리

=TRIM(SUBSTITUTE(A2,","," "))

---

SUBSTITUTE + TEXTJOIN

문자 조합 후 특정 문자 변경

=SUBSTITUTE(TEXTJOIN(",",TRUE,A2:A5),","," | ")

---

SUBSTITUTE + LEN

특정 문자 개수 계산

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

문자열 안에 "-"가 몇 개인지 계산할 수 있습니다.

---

실무에서 가장 많이 사용하는 사례

• 전화번호 하이픈 제거
• 주민등록번호 기호 제거
• 제품 코드 수정
• 이메일 주소 변경
• 회사명 일괄 변경
• 특수문자 삭제
• CSV 데이터 가공
• 공백 문자 변경

---

마무리

SUBSTITUTE 함수는 문자열 안의 특정 문자나 단어를 원하는 값으로 손쉽게 변경할 수 있는 매우 실용적인 함수입니다.

특히 전화번호 정리, 특수문자 제거, 파일명 수정, 데이터 전처리 등 다양한 실무에서 자주 활용됩니다.

REPLACE 함수와 함께 익혀두면 문자열 처리 작업을 훨씬 빠르게 수행할 수 있습니다.

---

엑셀 SUBSTITUTE 함수