예제 파일에서 첫 번째로 하고 싶은 것은 "다팔아 사원 목록"에서 주민등록번호 앞자리만 표시하고 나머지 뒷자리는 "*"로 대체하는 것입니다. 이건 이미 알아본 바 있습니다. LEFT 함수로 일부를 추출하고 & 기호를 사용하는 방법이었죠. 이 방법을 모르신다면 아래 강좌를 확인해 주세요.
REPLACE지정된 텍스트(old_text)에서 일부(start_num에서 num_chars 만큼)를 다른 텍스트(new_text)로 바꿉니다.
REPLACE(old_text, start_num, num_chars, new_text)| old_text | 일부 문자를 바꿀 대상 문자열. |
| start_num | old_text에서 바꿀 첫 문자의 위치. |
| num_chars | 바꿀 첫 문자의 위치(start_num)로부터의 개수.
|
| new_text | 바꿔 넣을 새 문자열. |
함수 정리는 위와 같습니다. MID 함수와 굉장히 유사합니다. 단지 바꿔 넣을 새 문자열 new_text 인수가 4번째 인수로 존재한다는 것이 다른 뿐 나머지 인수는 모두 MID 함수와 같습니다. 즉 old_text에서 일부 문자를 MID 함수처럼 추출하여 new_text로 교체하는 것입니다.
주민번호(보안)를 작성해 보겠습니다.
- 작성할 셀(G5)을 클릭하여 선택합니다.
- 수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =REPLACE(
- 첫 번째 인수 힌트(old_text)를 확인하고 참조로 값을 가져옵니다. => =REPLACE(D5
- 쉼표로 구분, 두 번째 인수 힌트(start_num)를 확인하고 가져올 문자의 시작 위치(8)를 입력합니다. => =REPLACE(D5,8
- 쉼표로 구분, 세 번째 인수 힌트(num_chars)를 확인하고 가져올 문자 개수(7)를 입력합니다. => =REPLACE(D5,8,7
- 쉼표로 구분, 네 번째 인수 힌트(new_text)를 확인하고 대체할 문자(*******)를 입력한 후 엔터(닫힌 괄호 자동 생성 후 셀 완성). => =REPLACE(D5,8,7,"*******")
주의! 문자는 항상 큰따옴표로 감싸서 사용. - 다시 완성된 G5셀을 클릭하고 나머지는 자동 채우기(우측 하단에서 클릭 드래그)로 완성합니다.
결과, 그림처럼 완성됩니다. 이전에 알아본 수식보다 훨씬 직관적으로 처리되었습니다.
=LEFT(D5,6)&"-*******" → =REPLACE(D5,8,7,"*******")
다음, 지금처럼 바꿀 문자열을 위치와 개수로 반환하지 않고, 특정 문자열 자체를 변경하는 함수도 있습니다. 예를 들어 위 예제의 이메일에서 "kaver"란 문자열을 "maber"로 변경하고 싶은 경우입니다. SUBSTITUTE 함수입니다.
SUBSTITUTE대상 문자열(text)에서 바꿀 문자열(old_text)을 찾아 새 문자열(new_text)로 교체합니다.
SUBSTITUTE(text, old_text, new_text, [instance_num])| text | 대상 문자열. |
| old_text | 바꿀 문자열. |
| new_text | 새 문자열. |
| instance_num | [선택] 대상 문자열에 바꿀 문자열(old_text)가 많을 경우 몇 번째를 바꿀 것인지 지정하는 수입니다.
- 지정하지 않을 경우 모든 old_text 바꾸며, 지정할 경우 해당 위치의 old_text만 바꿉니다. |
함수 정리는 위와 같습니다. 인수가 많아 보이지만 직관적이라 사용법이 간단합니다. 이메일에서 "kaver"를 "maber"로 바꿔보겠습니다.
- 작성할 셀(H5)을 클릭하여 선택합니다.
- 수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =SUBSTITUTE(
- 첫 번째 인수 힌트(text)를 확인하고 참조로 값을 가져옵니다. => =SUBSTITUTE(E5
- 쉼표로 구분, 두 번째 인수 힌트(old_text)를 확인하고 바꿀 문자열을 입력합니다. => =SUBSTITUTE(E5,"kaver"
- 쉼표로 구분, 세 번째 인수 힌트(new_text)를 확인하고 교체할 새 문자열 입력한 후 엔터(닫힌 괄호 자동 생성 후 셀 완성). => =SUBSTITUTE(E5,"kaver","maber")
- text에 바꿀 문자열 old_text가 여러 개 있지 않으니 네 번째 instance_num 인수를 작성할 필요 없습니다. - 다시 완성된 H5셀을 클릭하고 나머지는 자동 채우기(우측 하단에서 클릭 드래그)로 완성합니다.
결과, 그림처럼 이메일에 "kaver"가 모두 "maber"로 교체되었습니다.
추가로 SUBSTITUE 함수에서 instance_num을 사용하는 경우를 알아보겠습니다.
일반 전화 번호 표기법을 예를 들면, 방식은 다양하지만 보통 "OOO-OOO-OOOO"과 "OOO) OOO-OOOO"을 많이 사용합니다. 만약 "OOO-OOO-OOOO" 표기법을 "OOO) OOO-OOOO"로 바꾸고 싶다면 instance_num을 사용하여 간단하게 처리할 수 있습니다.
instance_num을 사용하지 않으면 B3셀처럼 모두 교체,
instance_num을 1로 사용하면 B4셀처럼 첫 번째만 교체,
instance_num을 2로 사용하면 B5셀처럼 두 번째만 교체합니다. 원하는 결과를 반환하려면 instance_num을 1로 사용해야 하는 것이죠.
이런 기능을 활용하면 위처럼 특정 기호를 원하는 문자로 변경하거나 삭제하는 데 효과적으로 사용할 수 있습니다.
의견글 입니다.
()
