엑셀 원하는 텍스트, 문자 바꿔주는 SUBSTITUTE 함수 활용 방법과 REPLACE 함수와의 차이점

실무에서 자주 활용하는 엑셀 함수 중 특정 문자, 텍스트를 다른 문자나 문자열로 대체해주는 함수가 있다.

바로 SUBSTITUTE 함수!

'대체하다' 라는 영어단어 SUBSTITUTE 그대로의 의미이다.

그런데 이런 대체하다를 의미하는 영어 단어가 하나 더 있다.

바로 REPLACE!

엑셀에는 REPLACE 함수도 있다.

SUBSTITUTE 함수를 어떻게 활용하는지, 어떤 상황에서 쓸 수 있는지,

그리고 REPLACE 함수와는 어떤 차이가 있는지 예제를 통해 쉽게 알아보자! 

 

엑셀 SUBSTITUTE 함수

문자열에서 내가 지정한 문자, 텍스트를 새로운 문자, 텍스트로 대체해주는 함수가 SUBSTITUTE 함수이다.

문자 뿐만 아니라 숫자도 대체할 수 있다.

 

=SUBSTITUTE(바꾸기 대상, 바꿀 텍스트, 바꾸려는 텍스트, [몇 번째 텍스트인지])
=SUBSTITUTE(text, old_text, new_text, [instance_num])

 

 

• 바꾸기 대상 : 찾고 바꾸려는 대상이 되는 텍스트
• 바꿀 텍스트 : 찾아서 new_text로 바꿀 텍스트, 대/소문자가 정확하게 일치해야지만 바꿀 수 있다.
• 바꾸려는 텍스트 : old_text와 바꾸려는 새로운 텍스트
• 몇 번째 텍스트인지 : 바꾸기 대상에 바꿀 텍스트가 여러개일 때 몇 번째 텍스트를 바꿀 것인지 선택할 수 있다.

 

예제를 통해 쉽게 이해해보자!

엑셀 SUBSTITUTE 함수 예제

 

지역이라는 컬럼에서 "서울신사" 라고 되어 있는 데이터를 세부 지명인 신사를 제외하고 "서울"만 표출되도록 하고 싶다면,

SUBSTITUTE 함수를 통해 "신사"라는 글자를 공백으로 만들어줄 수 있다.

바꾸기 대상은 "서울신사"가 있는 C3 컬럼이 될 것이다.

 

=SUBSTITUTE(C3  

 

신사라는 글자를 공백으로 바꿀 것이니 바꿀 텍스트는 "신사"가 될 것이고 바꾸려는 텍스트는 공백인 ""가 될 것이다.

 

=SUBSTITUTE(C3, "신사", "")

 

만약 새로운 지역을 세부 지명을 빼고 앞에 도시 기준으로만 모두 변환되도록 하고 싶다면 어떨까?

SUBSTITUTE 함수는 일일히 하나하나 바꿀 텍스트와 바꾸려는 텍스트를 입력해주어야 할 것이다.

똑같은 수식을 아래 "경기수원" 에 수식 붙여넣기를 한다면 결과는 그대로 "경기수원"으로 나올 것이다.

여기엔 "신사"라는 글자가 없기 때문이다.

그래서 SUBSTITUTE 함수는 컬럼마다 다른 문자, 텍스트가 있는 데이터를 바꾸기 보다는 동일한 문자를 바꾸고자 할 때 많이 쓴다.

 

예를들면, 날짜 컬럼에서 하이픈(-)을 모두 제외하고 싶을 때와 같은 경우이다.

참고로 날짜 데이터의 범주가 '날짜'로 되어 있으면 문자, 텍스트로 인식하지 않기 때문에,

날짜 데이터가 '일반' 범주로 되어 있어야 정상적으로 인식한다.

혹시 날짜를 입력했는데 자동으로 '날짜' 범주로 바뀐다면 앞에 따옴표(')를 넣어보자!

'2023-12-01

위와 같이 입력하면 문자, 텍스트로 인식한다.   

엑셀 SUBSTITUTE 활용 예제

 

여기서 날짜 컬럼이 있는 A3 에서 하이픈(-)을 모두 공백으로 바꾸기 위해 아래와 같이 수식을 입력했다.

 

=SUBSTITUTE(A3, "-", "")

 

그렇게 하고 수식 붙여넣기로 쭉 아래로 내리니 모두 동일하게 하이픈이 없는 문자로 결과값이 반환되었다.

그럼 지역 컬럼에서 앞의 큰 지역인 서울, 경기, 대구, 부산 등만 남기고 뒤의 세부 지역명은 없애는 새로운 지역을 넣기 위해서는 어떻게 해야할까?

이런 경우 REPLACE 함수를 사용하는 것이 좋다.

 

엑셀 REPLACE 함수

REPLACE 함수 역시 대체하다라는 의미인데 SUBSTITUTE 함수와는 사용법이 약간 다르다.

 

 =REPLACE(바꾸기 대상, 바꾸기 시작할 위치, 바꾸려는 문자의 개수, 바꾸려는 텍스트)
=REPLACE(old_text, start_num, num_chars, new_text) 

 

• 바꾸려는 텍스트 : 찾고 바꾸려는 대상이 되는 텍스트
• 바꾸기 시작할 위치 : 몇 번째 문자부터 바꾸기를 할 것인지
• 바꾸려는 문자의 개수 : 바꾸기 시작할 위치부터 몇 번째 문자까지 바꾸기를 할 것인지
• 바꾸려는 텍스트 : old_text와 바꾸려는 새로운 텍스트

 

SUBSTITUTE 함수와 비슷하지만 REPLACE 함수는 몇 번째부터 몇 번째 문자를 대체할 것인가? 이다.

 

위 예제에서 새로운 지역에서 어떻게 활용하는지 알아보자!

엑셀 REPLACE 함수 활용 예제

 

바꾸려는 텍스트는 마찬가지로 지역이 있는 컬럼인 C3 를 선택했고,

앞의 "서울"이라는 글자 뒷 부분을 없애기 위해서 바꾸기 시작할 위치는 세 번째 문자로 "3"을 선택했다.

 

=REPLACE(C3, 3 

 

지역에 따라 신사, 양재 등 두 글자인 경우도 있고 동성로, 해운대 등 세 글자인 경우가 있는데,

큰 지역 외에 뒷 글자는 모두 날리기 위해서 뒤가 가장 긴 글자수인 3을 입력해주었다.

 

=REPLACE(C3, 3, 3 

 

마지막으로는 세 번째 문자부터 그 다음 세 번째 문자까지는 공백으로 바꾸기 위해서 "" 으로 입력해주었다.

 

=REPLACE(C3, 3, 3, "")

    

이렇게 하고 수식 붙여넣기로 아래까지 쭈욱 내리면 동일하게 앞의 큰 지역만 남기고 값이 반환되는 것을 볼 수 있다.

상황에 따라 SUBSTITUTE 함수를 써야 편할 때가 있고 REPLACE 함수를 써야 편할 때가 있다.

"대체하다" 의미인 두 함수를 인지해서 상황에 따라 선택하여 활용해보자!