특정 문자를 다른 문자로 바꾸는 함수(REPLACE, SUBSTITUTE 함수)

텍스트에서 특정 문자열을 다른 문자열로 바꿀 때 사용하는 함수로 REPLACE 함수와 SUBSTITUTE 함수가 있습니다.

REPLACE 함수는 특정 위치에 있는 문자열을 다른 문자열로 바꿀 때 사용하고, SUBSTITUTE 함수는 텍스트의 특정 문자열을 다른 문자열로 바꿀 때 사용합니다. 즉, 다른 문자로 바꾸는 기능은 같지만 어떤 방식으로 바꾸느냐의 차이이죠.

 

물론 바꾸기 기능을 쓸 수도 있습니다. 단순히 문자 하나를 다른 문자로 바꾸는 정도는 바꾸기 기능으로 쓰는 게 더 편하고 빠릅니다. 하지만 실무에서 사용할 때 매번 동일한 작업을 반복해야 하고, 더 복잡한 상황에서는 사용하기 힘든 경우가 있습니다. 그리고 REPLACE와 SUBSTITUTE는 다른 함수와 중첩했을 때 막강한 파워를 자랑합니다.

 

이번 시간에는 두 함수의 기본 개념과 기능에 대해 익혀보겠습니다.

 

우선 REPLACE 함수에 대해 알아보겠습니다.

함수 구문과 인수를 알아보고 예제를 통해 배워보겠습니다.

 

 = REPLACE (old_text, start_num, num_chars, new_text)

인수	설명
Old_text	일부문자를 바꿀 문자열입니다.
Start_num	old_text에서 바꾸기를 시작할 위치입니다.
Num_chars	old_text에서 바꾸기를 할 문자의 개수입니다.
New_text	old_text에서 바꿔 넣을 새 문자열입니다.

예제를 통해 알아보겠습니다.

 

replace

D5에 있는 문자열 중 "-" 를 "//"로 바꾸었습니다.

"-"가 D5에서 3번째에 있는 문자열 하나이므로 2번째 인수에는 3, 3번째 인수에는 1이 들어갔습니다.

그리고 마지막 인수는 바꾸어줄 문자 "//"이 들어갔습니다. ""는 문자열이므로 들어갔습니다.

 

REPLACE 함수 자체로만 봐서는 간단한 함수라 인수에 대한 이해만 있으면 적용하기 어렵지 않을 겁니다.

 

그럼 다른 함수와 중첩해서 어떤 식으로 활용되는지 알아보겠습니다.

 

텍스트에서 어떤 문자나 단어의 위치 번호를 찾는 SEARCH 함수와 함께 사용해보겠습니다.

SEARCH 함수 대신 FIND 함수를 써도 상관없습니다. 주의할 점은 SEARCH 함수는 대소문자를 구분하지 않고 FIND 함수는 대소문자를 구분한다는 차이가 있습니다.

 

 

= SEARCH (Find_text, within_text, start_num)

인수	설명
Find_text	찾고자하는 텍스트입니다.
Within_text	find_text를 찾고자하는 텍스트입니다.
Start_num	within_text에 몇번째 문자부터 검색할지 정합니다.생략하면 1이 사용됩니다.

아래와 같은 품목코드가 있다고 가정합시다.

품목코드 중간에 하이픈(-)을 공백으로 바꿔보겠습니다. 24-568을 24 568로 말이지요.

REPLACE 함수가 문자열에서 문자의 위치에 따라 문자를 대체하는 함수인데

SEARCH 함수는 텍스트에서 문자의 위치 번호를 구해주는 함수이므로

두 개 함수를 중첩하면 특정 문자열에서 바꾸고자 하는 문자를 원하는 문자로 바꿀 수 있습니다.

 

이해를 돕기 위해 영상으로 한번 더 보시겠습니다.

 

 

 

이제 SUBSTITUTE 함수에 대해 알아보겠습니다.

 

구문과 인수를 먼저 보고 예제를 통해 알아보겠습니다.

 

 =SUBSTITUTE (Text, Old_text, New_text, Instance_num)

인수	설명
Text	바꾸기를 할 대상이 되는 텍스트
Old_text	New_text로 바꿀 텍스트
New_text	Old_text와 바꾸려는 새로운 텍스트
Instance_num	text에서 몇 번째에 있는 텍스트를 바꿀지 지정하는 수 생략시 모든 텍스트가 바뀜(선택요소)

그럼 예제를 보겠습니다. 앞서 배웠던 REPLACE 함수를 같이 보여드릴 테니 차이를 확인하시기 바랍니다.

SUBSTITUE 함수와 REPLACE 함수

이제 차이를 아시겠나요? 어쨌든 같은 기능입니다.

 

다른 예제를 하나 더 보겠습니다.

 

아래 예제는 1번부터 5번 문제까지의 채점 결과를 O와 X로 연결해서 표시한 것입니다. 하나의 셀에 다 들어가 있는 것이죠. 이걸 이용해 O의 개수를 세어서 20점을 곱한 점수를 구하려고 합니다. 각 셀에 O 나 X가 따로 입력되어 있으면 COUNTIF 함수로 O의 개수를 구하면 되지만 지금은 불가능하죠.

 

=5-LEN(SUBSTITUTE(E23,"O",""))

F23에 입력된 수식입니다. O의 개수를 구하는 방법이죠. SUBSTITUTE 함수로 채점 결과에서 O을 모두 빈 문자열("")로 바꿨습니다. 만약에 채점 결과가 OOXOX라면 SUBSTITUTE 함수의 결과는 XX가 되는 것이죠. 이 결과를 LEN 함수로 글자 수를 구하면 2가 됩니다. 원래 글자수 5에서 LEN 함수로 구한 X의 개수 2를 빼면 3이 나오는데 이것이 O의 개수가 됩니다.

 

=(5-LEN(SUBSTITUTE(E23,"O","")))*20

G23에 입력된 수식입니다. O의 개수 구하는 수식에서 20을 곱해줬습니다. 

 

이해를 돕기 위해 영상으로 보시겠습니다.

 

 

마지막으로 예제를 하나 더 보고 마치겠습니다.

어떤 셀에 입력된 문장에서 단어 개수를 세어 보겠습니다. 여기서 단어라고 하면 "새벽", "3시" 이렇게 한 뭉치로 있는 부분을 말합니다. 즉, 띄어쓰기 공백으로 구분된 단어를 말합니다.

SUBSTITUTE 함수 예제

=LEN(D40)-LEN(SUBSTITUTE(D40," ",""))+1

LEN(D40)은 분홍 셀에 입력된 글자 수입니다. SUBSTITUTE 함수는 분홍 셀에서 공백 (" ") 문자를 모두 빈 문자열 ("")로 바꿉니다. LEN 함수를 중첩해서 SUBSTITUTE 함수 결과로 반환된 글자 수를 구합니다. 즉, 전체 글자수에서 공백이 모두 없어진 D40의 글자수를 뺴주는 것이죠. 그럼 결국 공백의 개수가 나오게 되고 단어 개수는 1이 더 많으므로 1을 더해주면 위 문장에서 단어 개수를 구할 수 있습니다.

 

이밖에도 여러 가지 상황에서 유용하게 쓰이는데요. REPLACE 함수를 활용해 시트 이름을 가져오는 방법도 있는데 링크를 참조하시기 바랍니다.