SUBSTITUTE 함수로 위치에 관계없이 텍스트 바꾸기

엑셀 SUBSTITUTE 함수는 텍스트의 일부를 새로운 텍스트로 바꿔 줍니다.

비슷한 기능을 하는 REPLACE 함수는 바꿀 위치를 정확히 지정해야 하는 경우에 사용하지만 SUBSTITUE함수는 바꿀 위치에 상관없이 텍스트를 바꾸어야 할 때 사용합니다.

 

구문(Syntax)

 

사용 예

1) 한 개의 항목을 바꾸기

=SUBSTITUTE("종로구 익선동","익선","가회") => '종로구 가회동'
텍스트에서 '익선'을 '가회'로 바꿈

 

2) 여러 개의 항목을 한 번에 바꾸기

=SUBSTITUTE("책장에 있는 책을 책가방에","책","옷") => '옷장에 있는 옷을 옷가방에'
텍스트에서 '책'은 전부 '옷'으로 바꿈

=SUBSTITUTE("책장에 있는 책을 책가방에","책","옷",1) => '옷장에 있는 책을 책가방에'
네 번째 인수 instance_num이 1이므로 텍스트에서 첫 번째로 발견되는 것만 '옷'으로 바꿈

=SUBSTITUTE("책장에 있는 책을 책가방에","책","옷",2) => '책장에 있는 옷을 책가방에'
네 번째 인수 instance_num이 2이므로 텍스트에서 두 번째로 발견되는 것만 '옷'으로 바꿈

 

3) 텍스트에서 하이픈 제거하기

두 번째 인수는 "-", 세 번째 인수 new_text를 ""(빈값을 의미)으로 넣으면 "-"(하이픈)을 제거해줍니다.
전화번호, 사업자번호 등에서 숫자만 추출할 때 많이 사용합니다.

=SUBSTITUTE("010-2424-2424","-","") => '01024242424'
SUBSTITUTE 함수는 결과값을 텍스트 형식으로 반환하므로 값이 숫자로 변환되지 않고 맨 앞의 0이 유지 됨

=SUBSTITUTE("02-3460-6659","-","") => '0234606659'

=SUBSTITUTE("617-81-17517","-","") => '6178117517'

 

4) 영문자 바꾸기

SUBSTITUE 함수는 바꿀 값을 찾을 때 대소문자를 구분합니다

=SUBSTITUTE("삼성전자 2TB 외장 HDD","Hdd","SSD") => '삼성전자 2TB 외장 HDD'
'HDD'를 'Hdd'로 적으면 값이 바뀌지 않음

=SUBSTITUTE("삼성전자 2TB 외장 HDD","HDD","SSD") => '삼성전자 2TB 외장 SSD'
정확히 'HDD'인 것을 찾아서 'SSD'로 바꿈

 

[ 엑셀 텍스트 함수 ]

• TEXTAFTER 함수 – 구분기호 뒤의 텍스트 잘라내기
• TEXTBEFORE 함수 – 구분기호 앞의 텍스트 잘라내기
• TEXTSPLIT 함수 – 기호로 구분하여 텍스트 나누기
• 엑셀에서 텍스트(문자열) 다루기
• IF 함수에서 와일드카드가 안될 때
• 텍스트에서 마지막 단어 잘라내기
• TRIM 함수 - 공백 제거하기
• CLEAN 함수 – 인쇄할 수 없는 문자 제거
• LEFT 함수 – 왼쪽부터 텍스트 자르기
• MID 함수 – 중간부터 텍스트 자르기
• RIGHT 함수 – 오른쪽부터 텍스트 자르기
• UPPER 함수 - 텍스트를 대문자로 바꾸기
• LOWER 함수 - 텍스트를 소문자로 바꾸기
• PROPER 함수 - 첫 글자를 대문자로 바꾸기
• 엑셀에서 조건을 만족하는 텍스트 합치기(병합)
• SUBSTITUTE 함수로 위치에 관계없이 텍스트 바꾸기
• REPLACE 함수로 텍스트 바꾸기
• NUMBERVALUE 함수 - 지역 설정에 영향받지 않고 숫자로 변환
• T 함수 - 텍스트가 입력된 셀만 반환하기
• UNICHAR 함수 - 코드값에 해당하는 유니코드문자 반환
• CHAR 함수 - 코드값에 해당하는 문자 반환
• UNICODE 함수 - 문자의 유니코드값 구하기
• CODE 함수 - 문자의 코드값 구하기
• FIXED 함수 - 숫자 반올림 후 텍스트로 변환
• VALUE 함수 - 텍스트를 숫자로 변환하기
• EXACT 함수 - 두 텍스트가 같은지 비교하기
• SEARCH 함수 – 대소문자 구분없이 텍스트 위치 찾기
• TEXT 함수 - 숫자,날짜를 텍스트로 표시
• REPT 함수 - 텍스트를 반복해서 표시
• CONCATENATE 함수 – 텍스트 합치기
• CONCAT 함수 – 텍스트 합치기
• TEXTJOIN 함수 – 기호로 구분하여 텍스트 합치기
• LEN 함수 – 텍스트 길이 구하기
• FIND 함수 – 텍스트의 위치 찾기