엑셀 SUBSTITUTE 함수로 문자 바꾸기

엑셀이나 구글 스프레드시트에서 데이터를 다루다 보면 특정 문자를 다른 문자로 바꿔야 할 일이 종종 생깁니다. 수십, 수백 개의 셀을 일일이 수정하는 것은 비효율적이죠. 이럴 때 SUBSTITUTE 함수가 빛을 발합니다. SUBSTITUTE 함수는 텍스트 안의 특정 문자열을 원하는 다른 문자열로 바꿔주는 강력한 기능입니다.

SUBSTITUTE 함수의 기본 사용법부터 실무에서 유용한 꿀팁까지 모두 알려드리겠습니다.

 

 

SUBSTITUTE 함수의 기본 구조와 사용법

SUBSTITUTE 함수는 매우 직관적인 구조를 가지고 있습니다.

=SUBSTITUTE(텍스트, 찾을_문자, 바꿀_문자, [바꿀_순서])

• 텍스트: 변경하려는 문자열이 들어있는 셀 또는 직접 입력한 문자열입니다.
• 찾을_문자: 바꾸고자 하는 기존 문자열입니다.
• 바꿀_문자: 새롭게 넣고 싶은 문자열입니다.
• [바꿀_순서]: (선택 사항) 만약 '찾을_문자'가 텍스트 안에 여러 번 있을 경우, 몇 번째에 있는 것을 바꿀지 지정합니다. 이 인수를 생략하면 텍스트 안의 모든 '찾을_문자'를 바꿉니다.

예시를 통해 더 쉽게 이해해볼까요?

예시 1: 특정 문자를 모두 바꾸기

• 문제: '홍길동-2023-서울' 이라는 텍스트에서 모든 '-' 기호를 '/'로 바꾸고 싶습니다.
• 함수: =SUBSTITUTE(A1, "-", "/")
• 결과: '홍길동/2023/서울'

A1 셀에 있는 텍스트의 모든 '-'를 '/'로 바꿔줍니다.

 

예시 2: 특정 순서의 문자만 바꾸기

• 문제: '2023-11-20' 이라는 텍스트에서 두 번째에 있는 '-'만 바꾸고 싶습니다.
• 함수: =SUBSTITUTE(A2, "-", "/", 2)
• 결과: '2023-11/20'

'2'라는 숫자를 통해 두 번째에 있는 '-'만 바꾸라고 명령했습니다.

SUBSTITUTE 함수 활용 팁: 실무에서 써먹는 꿀팁!

SUBSTITUTE 함수는 단순히 문자열을 바꾸는 것을 넘어 다양한 상황에 응용할 수 있습니다.

1. 특정 문자를 공백으로 제거하기

데이터에 불필요한 공백이나 특정 기호(예: 괄호, 특수문자)가 섞여 있을 때 유용합니다.

• 문제: '(주)네이버'에서 '(주)'를 완전히 없애고 싶습니다.
• 함수: =SUBSTITUTE(A3, "(주)", "")
• 결과: '네이버'

'바꿀_문자'를 따옴표 두 개("")로 비워두면 해당 문자열을 제거하는 효과를 냅니다.

2. 중복된 공백을 하나로 만들기

텍스트 데이터에 여러 개의 공백이 연속으로 있을 경우, TRIM 함수와 SUBSTITUTE 함수를 조합하여 깔끔하게 정리할 수 있습니다.

• 문제: ' 서울역 - 강남역 '과 같이 불필요한 공백이 많습니다.
• 해결 방법: 먼저 SUBSTITUTE로 여러 공백을 하나의 공백으로 바꾼 후, TRIM으로 앞뒤 공백을 제거합니다.
• 함수: =TRIM(SUBSTITUTE(A4, " ", " "))
• 결과: '서울역 - 강남역'

" " (공백 두 개)를 " " (공백 한 개)로 계속 바꿔주는 방식을 응용합니다.

SUBSTITUTE vs. REPLACE: 차이점은?

엑셀과 구글 스프레드시트에는 SUBSTITUTE와 비슷한 REPLACE 함수도 있습니다. 두 함수의 가장 큰 차이점은 다음과 같습니다.

• SUBSTITUTE: '특정 문자'를 '다른 문자'로 바꿉니다. (문자열 기반)
• REPLACE: '특정 위치'의 '특정 길이'에 해당하는 문자를 바꿉니다. (위치 기반)

예를 들어, '123456789'라는 문자열에서 5번째 위치의 3글자('567')를 'ABC'로 바꾸려면 REPLACE 함수를 사용해야 합니다.

• REPLACE 함수: =REPLACE("123456789", 5, 3, "ABC")
• 결과: '1234ABC89'

상황에 따라 더 적합한 함수를 선택하여 사용하면 데이터 편집이 훨씬 효율적입니다.

결론

SUBSTITUTE 함수는 엑셀과 구글 스프레드시트에서 텍스트 데이터를 다루는 데 있어 필수적인 함수입니다. 단순한 문자열 교체부터 데이터 정제까지 다양한 용도로 활용할 수 있습니다. 오늘 배운 내용을 잘 기억하셔서 데이터 관리 작업을 더욱 효율적으로 만드시길 바랍니다.