엑셀 SUBSTITUTE · REPLACE · TEXT 함수로 텍스트 자동 수정하기 – 실무에서 반드시 쓰는 문자 가공 공식

엑셀에서 숫자보다 더 까다로운 것이 바로 문자(텍스트) 처리입니다.
고객 정보, 상품명, 날짜 형식, 코드 데이터 등을 다룰 때 불필요한 단어 제거, 특정 문자 치환, 형식 바꾸기 같은 작업은 피할 수 없습니다.
이때 가장 많이 활용되는 함수가 바로 SUBSTITUTE / REPLACE / TEXT입니다.

이번 글에서는 단순한 사용법이 아니라, 실무에서 정말 자주 발생하는 문제를 바로 해결할 수 있는 예시 중심으로 정리해드립니다. 문자 가공의 핵심 원리를 이해하면 어떤 데이터가 와도 쉽게 정리할 수 있게 됩니다.

---

1. SUBSTITUTE 함수 – 특정 문자를 다른 문자로 ‘치환’

형식:

=SUBSTITUTE(텍스트, 기존문자, 바꿀문자, [대상_번호])

SUBSTITUTE는 텍스트 안의 특정 문자를 찾아 원하는 문자로 바꿉니다.
특히 하이픈(-) 제거, 공백 제거, 특정 단어 치환에 가장 많이 사용됩니다.

📌 기본 예시

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

→ 결과: 01012345678 (전화번호 정리에 필수!)

📌 특정 단어 치환

=SUBSTITUTE("프리미엄 가죽 지갑", "프리미엄 ", "")

→ 결과: 가죽 지갑

📌 특정 번째만 치환하기

=SUBSTITUTE("A-B-C-D", "-", "/", 2)

→ 결과: A-B/C-D (두 번째 하이픈만 수정)

---

2. REPLACE 함수 – 특정 위치의 글자를 제거하거나 다른 텍스트로 변경

형식:

=REPLACE(텍스트, 시작위치, 삭제할_글자수, 새문자)

SUBSTITUTE가 “문자 자체를 찾아 치환”하는 방식이라면,
REPLACE는 “글자 위치 기반으로 변경”하는 함수입니다.
따라서 정확한 위치 기준으로 변경해야 할 때 훨씬 유용합니다.

📌 예시 1: 주민번호 뒷자리 마스킹

=REPLACE("900101-1234567", 8, 6, "******")

→ 결과: 900101-1******

📌 예시 2: 제품 코드 일부 변경

=REPLACE("A2024B57", 2, 4, "2025")

→ 결과: A2025B57

📌 예시 3: 전화번호 가운데 4자리 숨기기

=REPLACE("01012345678", 4, 4, "****")

→ 결과: 010****5678

---

3. TEXT 함수 – 숫자·날짜·시간을 원하는 형식으로 변환

형식:

=TEXT(값, "형식")

TEXT 함수는 숫자나 날짜를 문자열 형태로 꾸미는 기능입니다. 보고서 작성할 때 가장 강력한 도구 중 하나입니다.

📌 예시 1: 숫자를 3자리 콤마로 표시

=TEXT(1500000, "#,###")

→ 결과: 1,500,000

📌 예시 2: 날짜 형식 자유 변환

=TEXT("2025-11-18", "yyyy년 mm월 dd일")

→ 결과: 2025년 11월 18일

📌 예시 3: 시간 형식을 '오전/오후' 포함으로 변환

=TEXT("15:30", "오후 hh시 mm분")

→ 결과: 오후 03시 30분

---

4. SUBSTITUTE + MID + FIND 조합 – 실무에서 매우 많이 씀

📌 예제: "[특가] 프리미엄 가죽 지갑" → 대괄호 내용 제거

STEP 1) 대괄호 위치 찾기

=FIND("]", A1)

STEP 2) 대괄호 포함해 왼쪽 부분 제거

=MID(A1, FIND("]", A1)+1, 50)

→ 결과: 프리미엄 가죽 지갑

---

📌 예제: 날짜 데이터에서 하이픈 제거 후 연월일 하나로 결합

=SUBSTITUTE("2025-11-18", "-", "")

→ 결과: 20251118

보고서 파일명 생성할 때 자주 사용되는 방식입니다.

---

5. TEXT + TODAY + SUBSTITUTE로 자동 날짜 텍스트 만들기

예: “2025년 11월 18일 기준 데이터” 같은 문구를 자동으로 만들기

=TEXT(TODAY(), "yyyy년 mm월 dd일") & " 기준 데이터"

문서 자동화에서 매우 유용합니다.

---

6. 실무에서 자주 마주치는 P 형태 문제 예시

■ “서울특별시 강남구 테헤란로 123(2층)”에서 괄호 제거

=LEFT(A1, FIND("(", A1)-1)

→ 결과: 서울특별시 강남구 테헤란로 123

■ “홍길동 대표”처럼 공백이 여러 개일 때 공백 1개로 통일

두 번 반복 SUBSTITUTE:

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

→ 모든 이중 공백 제거

---

정리

SUBSTITUTE는 문자 자체를 바꾸는 함수
REPLACE는 문자 위치 기반으로 바꾸는 함수
TEXT는 숫자·날짜·시간 형식을 예쁘게 변환하는 함수

이 세 가지 함수만 능숙하게 사용해도 상품명 정리, 고객 정보 관리, 날짜 형식 통일, 보고서 자동 생성 등 실무 텍스트 처리의 대부분을 해결할 수 있습니다.

다음 글도 필요하시면 이어서 작성해드릴게요! 추천하는 다음 주제는 다음과 같습니다:

• “엑셀 TRIM, CLEAN 함수로 텍스트 오류(공백/숨은 문자)를 완벽 제거하기”
• “엑셀 FIND/SEARCH 응용 – 조건부 문자 분석 자동화”
• “엑셀에서 텍스트를 열 단위로 빠르게 분할하는 4가지 방법”

원하시는 주제를 말씀해주시면 바로 작성해드립니다!