엑셀 SUBSTITUTE 함수 사용법, 특정 글자 바꾸기/공백 제거 완벽 가이드

엑셀 SUBSTITUTE 함수 사용법, 특정 글자 바꾸기/공백 제거 완벽 가이드

 

데이터를 정리하다 '010-1234-5678'에서 하이픈(-)만 쏙 빼거나, '서울 특별시'를 '서울특별시'로 바꾸기 위해 '찾아 바꾸기(Ctrl+H)' 기능을 매번 실행하고 계신가요? 혹은 웹에서 복사한 데이터에 포함된 모든 공백을 제거하느라 시간을 낭비하고 있지는 않으신가요?

 

이렇게 특정 텍스트를 다른 문자로 바꾸거나 아예 없애는 작업은 데이터 정제의 핵심이지만, 수작업으로 처리하면 반복 작업으로 지칠뿐더러 원본 데이터까지 손상될 위험이 있습니다.

 

하지만 엑셀의 강력한 텍스트 편집 도구, SUBSTITUTE 함수 사용법만 알면 이 모든 고민을 한 번에 해결할 수 있습니다. 특정 글자를 원하는 문자로 자유자재로 바꾸거나, 특정 문자만 골라 삭제하는 데이터 클리닝 비법을 실무 예제와 함께 완벽하게 알려드립니다.

 

이 글의 마지막에는 바쁘신 분들을 위한 핵심 내용 3줄 요약도 준비되어 있습니다.

 

✨ 목차

• 1. 엑셀 SUBSTITUTE 함수란? (텍스트 치환의 마법사)
• 2. SUBSTITUTE 함수 사용법, 4가지 인수 완벽 분석 (기본 공식)
• 3. 실무 예제로 배우는 SUBSTITUTE 함수 (특정 문자 제거/바꾸기)
• 4. SUBSTITUTE 고수의 팁 (REPLACE 함수와의 차이점)

 

🤔엑셀 SUBSTITUTE 함수란? (텍스트 치환의 마법사)

🔗MS 오피스 공식 SUBSTITUTE 가이드

반응형

엑셀 SUBSTITUTE 함수는 '대체하다', '대신하다'라는 뜻의 영단어에서 유래했습니다. 그 이름처럼 지정한 텍스트 안에서 특정 문자(old_text)를 찾아 새로운 문자(new_text)로 바꿔주는, 즉 '치환'해주는 매우 강력한 텍스트 함수입니다.

 

예를 들어, "사과, 바나나, 사과"라는 텍스트에서 '사과'를 '오렌지'로 바꾸라고 명령하면 "오렌지, 바나나, 오렌지"라는 결과를 돌려줍니다. 또한, 특정 문자를 아예 없애고 싶을 때는 새로운 문자로 빈칸("")을 지정하여 '문자 삭제' 기능으로도 활용할 수 있습니다.

 

SUBSTITUTE 함수는 단순히 문자를 바꾸는 것을 넘어, 지저분한 데이터를 정제하고, 양식을 통일하며, 데이터 분석의 정확도를 높이는 데 필수적인 역할을 수행합니다.

 

 

⚙️SUBSTITUTE 함수 사용법, 4가지 인수 완벽 분석 (기본 공식)

SUBSTITUTE 함수의 공식은 4개의 인수로 구성되며, 마지막 인수는 선택적으로 사용할 수 있습니다.

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

각 인수가 어떤 역할을 하는지 정확히 이해하면, 어떤 텍스트 변환 작업도 자신 있게 처리할 수 있습니다.

1. text (원본 텍스트): 문자 바꾸기 작업을 수행할 원본 텍스트 또는 해당 텍스트가 들어있는 셀입니다.
2. old_text (바꿀 문자): 원본 텍스트에서 찾아 바꿀 기존 문자입니다. (예: "-", " ", "서울 특별시")
3. new_text (새 문자): 'old_text'를 대체할 새로운 문자입니다. 특정 문자를 삭제하고 싶다면, 이 인수에 큰따옴표 두 개("")를 입력하면 됩니다.
4. [instance_num] (선택 사항 - 바꿀 위치): 'old_text'가 텍스트 내에 여러 개 있을 경우, 몇 번째에 있는 문자만 바꿀지 숫자로 지정합니다. 만약 이 인수를 생략하면, 텍스트 내의 모든 'old_text'가 'new_text'로 바뀝니다.

'어떤 텍스트에서(text), 무엇을(old_text), 무엇으로 바꾸고(new_text), 몇 번째 것만 바꿀지(instance_num)' 이 4가지만 기억하면 됩니다.

 

 

📊실무 예제로 배우는 SUBSTITUTE 함수 (특정 문자 제거/바꾸기)

이제 실제 데이터로 SUBSTITUTE 함수의 강력함을 직접 확인해 보겠습니다. 아래 '연락처 및 주소' 표에서 전화번호의 하이픈(-)을 제거하고, 주소의 공백을 없애는 작업을 해보겠습니다.

원본 데이터	SUBSTITUTE 적용 결과
010-1234-5678	? (하이픈 제거)
서울 특별시 강남구	? (공백 제거)
2025/07/15	? ('/'를 '.'으로 변경)

예제 1: 전화번호에서 하이픈(-) 모두 제거하기

B2셀에 다음과 같이 수식을 입력합니다. 바꿀 문자인 '-'를 찾아서, 새 문자인 ""(빈칸)으로 대체합니다.
=SUBSTITUTE(A2, "-", "") → 결과: 01012345678

예제 2: 주소에서 모든 공백 제거하기

B3셀에 다음과 같이 수식을 입력합니다. 공백(" ")을 찾아서 ""(빈칸)으로 바꿉니다.
=SUBSTITUTE(A3, " ", "") → 결과: 서울특별시강남구

예제 3: 날짜 구분 기호 바꾸기

B4셀에 다음과 같이 수식을 입력합니다. '/'를 '.'으로 바꿉니다.
=SUBSTITUTE(A4, "/", ".") → 결과: 2025.07.15

 

 

💡SUBSTITUTE 고수의 팁 (REPLACE 함수와의 차이점)

SUBSTITUTE 함수를 제대로 사용하려면, 비슷한 기능을 가진 REPLACE 함수와의 차이점을 명확히 알아야 합니다. 이 둘을 헷갈리면 원치 않는 결과가 나올 수 있습니다.

🚀 핵심 포인트: SUBSTITUTE vs REPLACE, 결정적 차이

두 함수의 가장 큰 차이는 '무엇을 기준으로' 문자를 바꾸느냐에 있습니다.
• SUBSTITUTE: 텍스트의 '내용'을 기준으로 찾아서 바꿉니다. 셀 안에 있는 모든 "사과"를 "배"로 바꾸는 것처럼, 내용이 중요할 때 사용합니다. 대소문자를 구분합니다.
• REPLACE: 텍스트의 '위치'를 기준으로 바꿉니다. "3번째 글자부터 2개를" 다른 문자로 바꾸는 것처럼, 위치가 중요할 때 사용합니다.
데이터의 특정 '문자'를 일괄적으로 바꾸고 싶을 땐 SUBSTITUTE, 특정 '위치'의 글자만 바꾸고 싶을 땐 REPLACE를 사용한다고 기억하면 쉽습니다.

대부분의 데이터 정제 작업, 특히 불규칙한 데이터에서 특정 문자를 제거하거나 변경할 때는 위치에 구애받지 않는 SUBSTITUTE 함수가 훨씬 유연하고 강력한 성능을 발휘합니다.

🔗MS 오피스 공식 SUBSTITUTE 가이드

• SUBSTITUTE 함수는 텍스트 안의 특정 문자를 찾아 다른 문자로 바꾸거나, 빈칸("")으로 바꿔 삭제하는 '텍스트 치환' 함수입니다.
• 하이픈(-), 쉼표(,), 공백( ) 등 불필요한 문자를 일괄적으로 제거하여 데이터를 정제할 때 매우 유용합니다.
• REPLACE 함수와 달리 문자의 '내용'을 기준으로 작동하므로, 위치에 상관없이 특정 텍스트를 모두 변경하고 싶을 때 사용합니다.

오늘 알려드린 SUBSTITUTE 함수 사용법, 이제 지저분한 텍스트 데이터도 두렵지 않으시죠? 공감♥은 더 좋은 꿀팁을 만드는 에너지입니다!

SUBSTITUTE 함수로 데이터를 정리했다면, TRIM 함수로 양 끝의 공백을 추가로 제거하거나, LEFT/RIGHT/MID 함수로 원하는 부분만 추출하는 방법을 배워보세요. 여러분의 데이터 가공 능력이 한 단계 더 성장할 것입니다.

궁금한 점이나 다뤄줬으면 하는 다른 엑셀 함수가 있다면 언제든지 댓글로 남겨주세요!

#SUBSTITUTE함수 #엑셀SUBSTITUTE #엑셀텍스트바꾸기 #엑셀문자열치환 #엑셀공백제거 #엑셀특정문자제거 #REPLACE함수 #데이터정제 #직장인엑셀 #칼퇴