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

 

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

 

어쩌다보니 텍스트와 관련된 함수들을 계속해서 다루고 있습니다.

지금까지는 합치고 추출하는 방법들에 대해서 살펴보았습니다.

 

다양한 텍스트 작업을 할 때 유용하게 사용하고 계신가요?

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

 

 

이번에 알아볼 내용은 작성된 텍스트에서 필요한 부분만 바꾸고자 할 때 사용할 함수입니다.

 

개인정보가 노출된 데이터의 일부를 다른 텍스트로 변경해야 할 때,

하나씩 지우고 텍스트를 다시 입력해줘야할 필요가 없습니다.

 

이런 단순 반복을 최소화하기 위해 REPLACE 함수를 알아보겠습니다.

텍스트 추출 함수들을 이해하고 오셨다면, 쉽게 이해할 수 있습니다.

 

---

 

 

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

 

OS : Windows 10 Home

Program : Microsoft Office 365 Excel

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

 

 

 

---

 

 

1) 원본 데이터 입력하기

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

 

서울시청의 주소와 전화번호를 이용해보겠습니다.

 

 

 

2) REPLACE 함수로 텍스트 바꾸기

소중한 개인정보를 가려보겠습니다.

 

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

REPLACE(old_text, start_num, num_chars, new_text)
텍스트 문자열의 일부를 지정된 문자 수만큼 다른 텍스트 문자열로 바꿉니다.
old_text	일부 문자를 바꿀 문자열
start_num	old_text에서 new_text로 바꿀 문자의 위치
num_chars	old_text에서 new_text로 REPLACE할 문자의 수  (공백, 특수문자도 모두 +1)
new_text	old_text에 바꿔 넣을 새 문자열

 

 

REPLACE 함수는 이름에 걸맞게 직관적인 특성을 가지고 있습니다.

왠지 설명만 읽어보고 바로 사용해도 쓸 수 있을 정도로 쉬워보입니다.

그렇기 때문에 쉽게 생각하고 사용하시려다가 #NAME? 을 마주할 수 있습니다.

아래 내용을 읽지 않고 바로 사용해보시겠어요?

 

.

.

.

.

.

.

.

.

.

.

.

.

 

여기까지 내려오셨다는 건 성공하지 못했거나, 시간이 없어서 바로 내려오신 분들이겠지요 : >

오류를 범하지 않고 REPLACE 함수를 사용하기 위해서는 2가지를 주의하셔야 합니다.

 

 

첫 번째 주의사항은 start_num에 입력할 문자의 위치입니다.

 

텍스트 추출 함수들을 배우셨다면 쉽게 넘어가실 수 있을 것입니다.

모르시는 분들은 아래 설명을 보시고 이해하시면 됩니다. :)

 

 

도로명 주소의 각 문자와 공백들을 엑셀에서 인식하고 있는 숫자와 매칭 시켜보았습니다.

공백과 특수문자도 모두 +1 해야 한다는 게 어떤 의미인지 확실히 이해가 될 것이라 생각합니다.

 

 

두 번째 주의사항은 new_text에 입력할 문자열입니다.

 

엑셀이 인식할 수 있는 문자열이란 쌍따옴표(")로 둘러싸인 텍스트를 의미합니다.

만약 * 로 바꾸고 싶으시면, "*" 와 같이 입력을 하셔야 엑셀이 인식을 할 수 있습니다.

 

 

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

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

 

REPLACE 함수를 이용해서 위치의 첫 두글자를 - 로 변경해보겠습니다.

 

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

첫 번째 문자에서부터 시작하기 위해 1을 입력하고,

변경하고자 하는 글자수 2를 입력하고,

변경하려는 글자수에 맞게 "-"를 붙여보았습니다.

 

 

REPLACE 함수를 이용해서 주소의 도로명칭을 + 로 변경해보겠습니다.

 

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

도로명칭의 시작인 "덕"의 위치 10을 입력하고,

변경하고자 하는 글자수 13을 입력하고,

변경하려는 글자수에 맞게 "+"를 붙여보았습니다.

 

 

REPLACE 함수를 이용해서 전화번호의 끝 4자리를 * 로 변경해보겠습니다.

 

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

끝 4자리의 시작인 "2"의 위치 8을 입력하고,

변경하고자 하는 글자수 4을 입력하고,

변경하려는 글자수에 맞게 "*"를 붙여보았습니다.

 

 

3) 짜라란!

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

 

의도한대로 위치, 주소, 전화번호의 데이터가 변경된 것을 확인할 수 있습니다.

 

 

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

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

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

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

 

---

 

텍스트의 특정 위치에서부터 일괄로 변경해야 할 때 사용하면 유용하겠죠?

개인정보를 가리는 것 외에도 데이터를 다루다보면 필요한 경우가 종종 있습니다. :)

 

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