엑셀 문자열 치환 SUBSTITUTE

대량의 텍스트 데이터에서 특정 문자나 단어를 빠르고 정확하게 원하는 값으로 일괄 치환해야하는 경우가 있습니다.

수작업으로 일일이 텍스트를 수정하는 번거로움과 시간 소모를 줄이고, 데이터 정제 및 가공의 자동화 수준을 크게 끌어올릴 수 있는 방법이 필요합니다.

 

SUBSTITUTE 함수란?

엑셀에서 텍스트 문자열 내에서 특정 문자나 단어를 다른 문자나 단어로 바꿔주는 함수입니다.

데이터 정제, 텍스트 가공, 불필요한 문자 제거 등 다양한 데이터 분석 및 콘텐츠 제작 작업에서 매우 자주 사용됩니다.

기본 구문

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

• text: 원본 텍스트(셀 참조 또는 직접 입력)
• old_text: 바꿀 문자 또는 단어
• new_text: 새로 넣을 문자 또는 단어
• instance_num: (선택) 몇 번째 등장하는 old_text만 바꿀지 지정. 생략하면 모든 old_text를 new_text로 바꿈
  
  

예시

• =SUBSTITUTE("010-1234-5678", "-", "")
  → 결과: "01012345678" (모든 하이픈 제거)
• =SUBSTITUTE("banana", "a", "o")
  → 결과: "bonono" (모든 a를 o로 변경)
• =SUBSTITUTE("2025-06-06", "-", "/", 2)
  → 결과: "2025-06/06" (두 번째 하이픈만 슬래시로 변경)
  
  

활용 팁

• 전화번호, 주민등록번호 등에서 구분 문자 일괄 제거
• 특정 단어나 기호를 일괄적으로 다른 값으로 치환
• instance_num을 활용해 원하는 위치만 선택적으로 변경 가능

SUBSTITUTE 함수는 데이터 분석과 콘텐츠 제작에서 텍스트 변환 작업을 자동화하는 데 매우 유용합니다.

 

 

실무에서 자주 활용되는 예제들

1. 전화번호에서 하이픈(-) 제거

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

• 결과: "01012345678"
• 활용: 데이터 분석 시 전화번호를 숫자만 남기고 정제할 때 유용합니다.
  
  

2. 특정 단어 일괄 변경

=SUBSTITUTE("사과, 배, 사과, 감", "사과", "오렌지")

• 결과: "오렌지, 배, 오렌지, 감"
• 활용: 콘텐츠 제작 시 반복되는 단어를 한 번에 바꿀 수 있습니다.
  
  

3. 특정 위치의 문자만 변경

=SUBSTITUTE("2025-06-06", "-", "/", 2)

• 결과: "2025-06/06"
• 활용: 날짜 형식 등에서 두 번째 구분자만 바꿔야 할 때 instance_num 인수를 사용합니다.
  
  

4. 줄바꿈(CHAR(10)) 제거

=SUBSTITUTE(A1, CHAR(10), "")

• 결과: 셀 A1의 줄바꿈 문자가 모두 제거됨
• 활용: 여러 줄로 입력된 데이터를 한 줄로 정제할 때 자주 사용합니다.
  
  

5. 여러 기호를 한 번에 제거 (중첩 사용)

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

• 결과: A1 셀의 하이픈과 공백이 모두 제거됨
• 활용: 데이터 분석 전 불필요한 특수문자와 공백을 동시에 없앨 때 활용합니다.
  
  

이처럼 SUBSTITUTE 함수는 데이터 분석과 콘텐츠 제작에서 텍스트를 손쉽게 변환하는 데 매우 효과적입니다.