엑셀 글자 바꾸기(찾아바꾸기, REPLACE,SUBSTITUTE함수)

REPLACE 함수 이용하기

REPLACE 함수를 이용해서 셀의 특정문자를 다른 문자로 바꾸어 보자.
REPLACE 함수는 규칙성이 있는 값들에 적용을 해야 효과가 있다.
가령 예컨대 주민등록번호, 전화번호 등과 같이 일정한 규칙을 가진 문자값들을 말한다.

REPLACE 함수는 아래와 같은 구조를 가진다.
=REPLACE(Old_text, Start_num, Num_chars, New_text)

㉠Old_text : 텍스트를 바꿀 주소
㉡Start_num : 시작숫자. 즉 몇 번째 글자부터 바꾸어줄까요이다.
㉢Num_chars : 그럼 시작점 포함해서 몇개를 바꾸어 줄까
㉣New_text : OK! 바꿀 문자를 적어주세용

아래의 예제를 보자.

A1에 있는 623456 - 1234567 값에서 주민등록번호 뒷 첫자리를 제외하고 별표로 표시하고 싶다면

먼저 바꿀 셀인 A1을 선택한다.

=REPLACE(A1,

다음 시작숫자가 몇번째인지 찾아야 된다. 234567을 ******로 바꾸니 2가 시작되는 순서는 공백포함해서 11번째에 2가 존재한다.

=REPLACE(A1,11,

다음 2를 시작으로 234567인 6문자를 바꾸니

=REPLACE(A1,11,6

마지막으로 대체될 문자를 따옴표 사이에 입력해주면 된다.

=REPLACE(A1,11,6,"******")

4번째 인수인 대체값의 텍스트는 무조건 "따옴표" 를 입력해야 오류가 없다.

단, 숫자는 따옴표 생략가능하다.

1번은 위에서 한 정상적인 주민번호 뒤 2번째 자리부터 *표시로 바뀐 모습이다.

2번은 3번째인수인 몇개를 바꾸어 줄까에서 0을 입력했을 때이다.

이 때는 대체문자없이 민증 뒤에서 두번째 숫자 앞에 ******가 입력된 것을 알 수 있다.

3번은 4번째 인수값이 텍스트가 아닌 숫자를 입력했을 때 ""따움표를 생략한 것이다.

SUBSTITUTE 함수 이용하기

SUBSTITUTE는 사전적 의미로 1. (다른 누구・무엇을) 대신하는 사람, 대리자; 대용물, 대체물   2. 교체 선수이다.

SUBSTITUTE 함수는 특별한 경우는 제외하고

=SUBSTITUTE(대상 셀, 없어질 텍스트, 새로 나올 텍스트)로 3개의 인수로 이루어져 있다.

아래 예제는 주민번호나 전화번호처럼 일정한 규칙을 가지고 있지 않는 뒤죽박죽의 텍스트들 중에서 서울을 부산으로 변경한 모습이다.

=SUBSTITUTE(대상 셀, 없어질 텍스트, 새로 나올 텍스트)에서

먼저 대상셀은 A1셀이다.

=SUBSTITUTE(A1,

다음 서울이라는 글자를 없애고 싶으니 따옴표와 함께

=SUBSTITUTE(A1,"서울"   을 입력했다.

마지막으로 부산이라는 글자가 대체텍스트로 나오므로

=SUBSTITUTE(A1,"서울","부산") 으로 입력해주면 끝이다.

특정한 경우를 보자.

마지막 instance_num 인수는

셀에 중복된 문자가 있을 수 있으니 몇번째 나오는 값을 변경해 줄까이다.

아래 그림을 보면

4번째 인수가 없을때는 서울이 모두 부산으로 대체되었다.

4번째 인수를 1로 했을 때 앞에 있는 즉 1번째 있는 서울만 부산으로 대체한 것을

4번째 인수를 2로 했을 때 두번째로 나오는 서울만 부산으로 대체된 것을 확인할 수 있다.

엑셀 찾아바꾸기

[ 홈 ] -> [ 편집 탭 ] -> [ 찾기 및 선택 ] -> [바꾸기 ]를 선택한다. 혹은 단축키인 Ctrl +H를 누른 뒤 찾기 및 바꾸기로 이동하자.

마우스로 영역을 지정해도 되지만 그냥 해보자.

부산을 부산시로 변경하기 위해

찾을 내용에 부산을 바꿀 내용에 부산시를 입력했다.

모두 바꾸기를 선택하면 한번에 모두 변경이 된다.

바꾸기를 선택하면 현재 부산이라는 선택된 셀만 변경이 된다.

다음 찾기를 선택한 후 하나씩 찾을 수도 있고, 모두 찾기를 클릭해 한번에 모두 다 찾을 수도 있다.

그럼 부산이라는 글자가 부산시로 변경이 되었다.

B4셀을 보면 원래는 부산시였는데 부산 ->부산시로 되어서

부산시시로 변한 걸 알 수 있다.

이번엔 옵션창을 활성화 시켜 전체 셀 내용 일치를 체크한 후 바꾸기를 해보자.

즉 찾을 내용 부산이라는 값과 똑같은 값을 가진 셀만 변경한다는 뜻이다.

부산시는 부산 + 시이므로 부산과 똑같은 값이 아니다.

2개의 항목이 바뀌었습니다라며 부산 -> 부산시로 변경되었다.

원래 B4셀의 부산시는 그대로인것을 확인할 수 있다.