단순히 데이터를 입력하는 단계를 넘어, 수천 개의 셀에 박힌 특정 문자를 한꺼번에 바꾸거나 개인정보를 마스킹 처리해야 하는 상황이 빈번하죠.
이때 가장 많이 헷갈리는 것이 바로 REPLACE와 SUBSTITUTE 함수입니다.
결론부터 말씀드리면, “어디를 바꿀까?”를 고민한다면 REPLACE를, “무엇을 바꿀까?”를 고민한다면 SUBSTITUTE를 선택해야 합니다.
이 차이만 명확히 알아도 업무 시간이 절반으로 줄어드는 기적을 경험하실 겁니다.
1. 위치의 절대 강자, REPLACE 함수 이해하기
데이터를 다루다 보면 글자 내용과 상관없이 특정 위치에 있는 값만 바꿔야 할 때가 있습니다.
예를 들어 휴대폰 번호의 가운데 네 자리를 별표(*)로 가리거나, 사번의 앞자리 두 개만 변경하는 경우죠.
이럴 때는 REPLACE 함수가 구원 투수로 등장합니다.
제가 실무에서 가장 유용하게 썼던 케이스는 고객 명단의 전화번호 중간을 일괄적으로 가리는 작업이었습니다.
문자열이 무엇이든 상관없이 ‘네 번째 글자부터 네 글자’라는 좌표값만 주면 되니까요.
=REPLACE(기존_텍스트, 시작_위치, 바꿀_글자수, 새_텍스트)
* 기존_텍스트: 원본 데이터가 있는 셀입니다.
* 시작_위치: 몇 번째 글자부터 바꿀지 정합니다.
* 바꿀_글자수: 몇 글자를 지울지 결정합니다.
* 새_텍스트: 그 자리에 새로 넣을 내용입니다.
하지만 조심해야 할 점도 있습니다.
글자 수가 일정하지 않은 데이터(예: 이름의 길이가 2자에서 4자까지 섞인 경우)에 고정된 숫자를 넣으면 결과가 엉망이 될 수 있습니다.
그래서 REPLACE는 규격화된 데이터, 즉 형식이 일정한 텍스트 작업에 최적화되어 있습니다.
2. 특정 문자를 저격하는 SUBSTITUTE 함수
반대로 위치는 제각각인데 특정 단어만 골라서 바꾸고 싶다면 어떨까요?
예를 들어 “서울특별시 강남구”에서 “서울특별시”를 “서울시”로 바꾸고 싶을 때 말입니다.
이때는 SUBSTITUTE 함수가 정답입니다.
이 함수는 위치를 계산할 필요가 없습니다.
그냥 “A라는 글자를 찾아서 B로 바꿔줘”라고 말하면 끝이니까요.
개인적으로 데이터 클렌징 작업을 할 때 공백을 제거하거나 오타를 일괄 수정하는 용도로 정말 자주 사용합니다.
=SUBSTITUTE(텍스트, 찾을_문자, 바꿀_문자, [바꿀_순번])
* 텍스트: 원본 데이터입니다.
* 찾을_문자: 바꾸고 싶은 특정 텍스트입니다.
* 바꿀_문자: 새로 교체할 텍스트입니다.
* 바꿀_순번: (선택) 몇 번째로 나오는 글자를 바꿀지 정합니다. 생략하면 전부 바뀝니다.
여기서 [바꿀_순번]이라는 옵션이 진짜 꿀팁입니다.
문장 안에 “사과”라는 단어가 세 번 나오는데, 두 번째 사과만 바꾸고 싶다면 이 숫자를 ‘2’로 쓰면 됩니다.
이런 유연함 덕분에 문장형 데이터를 다룰 때 SUBSTITUTE는 거의 필수적인 존재가 됩니다.
3. 한눈에 보는 REPLACE vs SUBSTITUTE 차이점
두 함수의 차이를 명확하게 구분하기 위해 표로 정리해 보았습니다.
상황에 맞는 도구를 선택하는 것이 엑셀 고수로 가는 지름길입니다.
| 구분 | REPLACE | SUBSTITUTE |
|---|---|---|
| 교체 기준 | 글자의 위치(숫자) | 특정 문자 내용 |
| 주요 용도 | 마스킹, 사번 수정 | 오타 수정, 공백 제거 |
| 데이터 형식 | 규격화된 데이터 | 불규칙한 문장 데이터 |
| 강점 | 정확한 좌표 지정 | 대소문자 구분 및 검색 |
참고로 SUBSTITUTE는 대소문자를 엄격하게 구분한다는 점을 잊지 마세요.
만약 소문자 ‘apple’을 바꾸라고 시켰는데 원본에 대문자 ‘APPLE’이 있다면 함수는 꿈쩍도 하지 않을 겁니다.
반면 REPLACE는 위치 기반이라 대소문자 따위는 신경 쓰지 않고 무조건 그 자리를 도려내고 새 글자를 박아 넣습니다.
4. 실전 활용 사례: 어떤 함수를 써야 할까?
이론만으로는 부족하죠.
우리가 실무에서 마주치는 실제 사례들을 통해 어떤 함수가 효율적인지 선택해 보겠습니다.
💡 실전 선택 가이드
- 사례 1: 주민번호 뒷자리 숨기기 -> REPLACE 추천 (8번째 자리부터 7자를 ‘*’로 교체)
- 사례 2: 주소에서 ‘동’을 ‘로’로 바꾸기 -> SUBSTITUTE 추천 (‘동’이라는 글자를 찾아 교체)
- 사례 3: 제품 코드의 앞 3자리 변경 -> REPLACE 추천 (1번째부터 3자를 교체)
- 사례 4: 보고서 내의 불필요한 줄바꿈 제거 -> SUBSTITUTE 추천 (줄바꿈 문자를 찾아 빈칸으로 교체)
그런데 만약 두 상황이 복합적으로 얽혀 있다면 어떨까요?
예를 들어 “2023-12-18″이라는 날짜 형식에서 하이픈(-)을 점(.)으로 바꾸면서 연도만 숨기고 싶다면?
이럴 때는 두 함수를 중첩해서 사용하거나, LEFT/RIGHT 함수와 조합하는 숙련도가 필요합니다.
하지만 기초가 탄탄하다면 응용은 시간문제일 뿐입니다.
5. 자주 묻는 질문 (FAQ)
네, 가능합니다! =SUBSTITUTE(A1, ” “, “”) 처럼 찾을 문자에 공백(” “)을 넣고 바꿀 문자에 아무것도 없는 문자열(“”)을 넣으면 모든 띄어쓰기가 사라집니다.
REPLACE와 SUBSTITUTE는 모두 텍스트 함수입니다. 결과값이 숫자로 보이더라도 실제로는 문자 형식으로 저장되니, 산술 계산이 필요하다면 결과값 앞에 VALUE 함수를 붙여주세요.
REPLACE 함수에서 ‘바꿀_글자수’ 부분에 LEN 함수를 활용해 원본 데이터의 총 길이를 넣어주면 시작 지점부터 끝까지 시원하게 교체할 수 있습니다.
마무리하며: 도구의 차이가 업무의 품격을 만듭니다
결국 엑셀은 논리의 싸움입니다.
내가 가진 데이터의 성격을 먼저 파악하고, 위치가 중요한지 내용이 중요한지를 판단하는 능력이 중요하죠.
오늘 배운 REPLACE와 SUBSTITUTE의 차이점만 제대로 활용해도, 동료들이 한 시간 걸릴 노가다 작업을 여러분은 10초 만에 끝낼 수 있습니다.
처음에는 수식이 낯설 수 있지만, 직접 몇 번 타이핑하다 보면 손이 먼저 기억하게 될 것입니다.
위치를 알 때는 REPLACE, 내용을 알 때는 SUBSTITUTE를 쓴다.
SUBSTITUTE는 대소문자를 구분한다는 점을 기억한다.
마스킹 작업(주민번호, 전번)에는 REPLACE가 훨씬 편하다.
공백 제거와 특정 단어 일괄 수정은 SUBSTITUTE가 정답이다.
다음 번에는 이 함수들을 응용해 더 복잡한 데이터 정제 작업을 자동화하는 방법을 소개해 드리겠습니다.
여러분의 칼퇴근을 진심으로 응원합니다!
