[아.. 엑셀좀 해놓을껄]엑셀에서 SUBSTITUTE 함수로 텍스트 바꾸기

 

간단하고 빠르게 해결하는 딘도르입니다.

 

전편에서는 REPLACE 함수를 이용한 개인정보를 모두 * 로 변경해보았습니다.

이제 범위를 지정하여 텍스트를 일괄로 변경하는데 어려움이 없으실 거라 믿습니다.

 

텍스트 합치기	빠른 채우기	CONCATENATE 함수	CONCAT 함수	TEXTJOIN 함수	합치기 기능 비교
텍스트 추출하기	LEFT 함수	RIGHT 함수	MID 함수
텍스트 바꾸기	REPLACE 함수

 

 

이번에 알아볼 내용은 작성된 텍스트에서 특정 단어를 찾아 변경하고 싶을 때 사용할 수 있는 함수입니다.

 

일을 하다 보면 A회사와 B회사를 언급했을 때 순서를 변경해야 하는 것처럼

특정 단어들을 모두 교환해야 할 경우가 심심치 않게 발생합니다. 

 

이럴 때마다 단어를 찾아 순서대로 변경해줄 필요가 없습니다.

SUBSTITUTE 함수를 이용하면 손쉽게 해결할 수 있습니다.

---

 

 

엑셀에서 SUBSTITUTE 함수로 텍스트 바꾸기

 

OS : Windows 10 Home

Program : Microsoft Office 365 Excel

타 버전에서도 동작할 수 있는 기능입니다. :)

 

 

 

---

 

 

1) 원본 데이터 입력하기

작업에 사용할 데이터를 입력해보겠습니다.

 

AA 회사와 BB 회사가 작성한 계약서라 가정해보았습니다.

 

 

 

2) SUBSTITUTE 함수로 회사명 바꾸기

회사 이름을 서로 바꿔보도록 하겠습니다.

 

먼저 SUBSTITUTE 함수가 무엇인지 알아보겠습니다.

SUBSTITUTE(text, old_text, new_text, instance_num)
텍스트 문자열의 old_text를 nex_text 로 바꿉니다.
text	일부 문자를 바꿀 문자열
old_text	변경해야 할 문자열
new_text	old_text에 바꿔 넣을 새 문자열
instance_num	입력 O -> 몇 번째 old_text를 new_text로 바꿀지 지정 입력 X -> 모든 old_text가 new_text로 변경

 

 

SUBSTITUE 함수도 설명만 봤을 때 어려움 없이 사용할 수 있을 것 같습니다.

모든 텍스트 함수가 동일하게 텍스트를 입력하는 곳에는

"" 안에 넣어야 한다는 것만 주의해주시면 됩니다.

안 그러면 #NAME?을 마주하실 수 있습니다.

 

주의사항은 old_text, new_text에 입력할 문자열입니다.   엑셀이 인식할 수 있는 문자열이란 큰따옴표(")로 둘러싸인 텍스트를 의미합니다. 만약 * 로 바꾸고 싶으시면, "*"와 같이 입력을 하셔야 엑셀이 인식을 할 수 있습니다.

 

 

자! 그럼 말씀드렸던 주의사항을 잘 기억하시고 실습을 진행해보도록 하겠습니다.

차례대로 실습을 진행하고 결과는 마지막에 한 번에 보도록 하겠습니다.

 

substitute 함수를 이용해서 순차적으로 AA <-> BB로 변경해보겠습니다.

단, 모든 회사명을 변경하지 않고, 각 회사가 언급된 첫 번째 회사명만 교체해보겠습니다.

내가 원하는 위치에 단어를 바꾸는 것이 이 함수의 포인트이기 때문입니다.

 

첫 번째 예시에는 AA와 BB가 한 번씩 언급됐기 때문에 

 

변경하려는 문자열 A1를 선택하고,

변경하고자 하는 "AA"를 old_text 란에

변경할 "BB"를 nex_text 란에 입력합니다.

 

 

두 번째 예시에는 AA가 두 번, BB가 한 번 언급됐기 때문에

 

변경하려는 문자열 A2를 선택하고,

변경하고자 하는 "AA"를 old_text 란에

변경할 "BB"를 nex_text 란에 입력하고

첫 번째 "AA"라는 의미에서 1을 instance_num에 입력합니다.

 

 

세 번째 예시에는 AA가 한 번, BB가 두 번 언급됐기 때문에

 

변경하려는 문자열 A2를 선택하고,

변경하고자 하는 "AA"를 old_text 란에

변경할 "BB"를 nex_text 란에 입력합니다.

 

"AA"가 한 번 언급됐기 때문에 instance_num은 입력할 필요가 없습니다.

 

 

3) 변경된 결과 확인하기

첫 번째 "AA"가 "BB"로 변경됐는지 확인합니다.

 

각 예시에서 첫 번째 "AA"가 모두 "BB"로 변경됨을 알 수 있습니다.

 

그럼 이제 다음 단계에서 무엇을 할지 감이 오시나요?

네! 동일하게 "BB"를 "AA"로 변경하려고 합니다.

 

 

4) SUBSTITUTE 함수로 회사명 바꾸기

회사 이름을 서로 바꿔보도록 하겠습니다.

 

동일한 방법으로 "BB" -> "AA"로 교체해보겠습니다.

 

각 회사가 언급된 첫 번째 회사명만 교체하기로 하였고,

첫 번째 AA를 BB로 교체했으므로 기존 첫 번째 BB는 순번이 +1이 되었습니다.

그래서 우리는 두 번째 나오는 BB를 모두 AA로 교체해야 합니다.

 

모든 예시에는 두 번째 BB를 교체해주면 되기 때문에 

 

변경하려는 문자열을 선택하고,

변경하고자 하는 "BB"를 old_text 란에

변경할 "AA"를 nex_text 란에 입력하고

두 번째 "BB"라는 의미에서 2를 instance_num에 입력합니다.

 

3) 짜라란!

원하는 기능이 동작하는지 확인합니다.

 

의도한 대로 각 회사가 언급된 첫 번째 회사명만 교체된 것을 확인할 수 있습니다.

 

변경된 문자열은 함수를 이용하여 원본 데이터를 참조하고 있기 때문에 항상 원본 데이터의 영향을 받습니다.

원본 데이터를 삭제해야 하거나, 변경된 문자열을 다른 곳으로 복사해야 한다면

반드시 복사 - 선택하여 붙여넣기 - 값을 해주시기 바랍니다.

그래야 독립적인 문자열이 되기 때문에 원본 데이터의 영향을 받지 않게 됩니다.

 

---

 

실습으로 텍스트 -> 텍스트 변경을 했지만, 텍스트 -> 공백 or 숫자를 통해서 응용한다면,

숫자만 추출하거나 반대로 문자만 추출하는 방법도 가능합니다.

 

텍스트의 특정 단어들을 위치를 지정하여 일괄로 변경해야 할 때 사용하면 유용하겠죠?

데이터를 다루다 보면 심심치 않게 자주 필요한 기능이니 꼭 알아두시면 퇴근을 앞당길 수 있습니다 :)

 

알고 보면 어렵지 않지만 막상 방법이 떠오르지 않아 고생하셨던 분들에게 도움이 됐으면 합니다.