115_엑셀 초급 강좌 – 함수 중급 (42) 

엑셀에서 해당된 문자를 바꾸는 함수는 replace와 substitute이 가장 많이 쓰입니다. 둘의 사전적 의미는 거의 비슷한데 replace는 교환하다는 단순한 느낌인 것 같으면 substitute은 교환해서 먼가 대체를 해준다 라는 어감이 드네요. 교수님이 만약 수업을 못 들어가서 내가 들어갈 때는 substitute, 그리고 부품이 고장 나서 교환 할 때는 replace를 자주 씁니다. 엑셀에서 두 함수의 차이점은 사전적 의미와는 상관없는 것 같네요. replace는 문자를 바꿀 때 바꿀 문자의 위치 정보를 인수로 받지만 substitute은 바꿀 문자의 문자 정보를 인수로 받습니다. 엑셀 질문들을 보면 주민등록번호 형태, 또는 잘못 입력된 시간 및 날짜 등을 바꾸는 질문들이 많은데요 이 두 함수로 대부분의 문제점을 해결 합니다.

115연습파일.xlsx

115연습파일_완성본.xlsx

  1. REPLACE함수로 문자 바꾸기

    함수 사용법

    REPLACE(바꿀셀,시작위치,문자개수,새로운문자)는 이전 바꿀 셀에서 새로운 문자로 바꾼 값을 반환합니다.

    Replace 함수의 특징은 바꿀 문자의 위치를 인수로 받는 다는 것인데요 내 데이터가 정확한 패턴을 가지고 있을 때만 유용합니다. 그리고 이 함수는 한 셀에서 두 군데 이상의 내용을 단일 함수로는 바꿀 수 없습니다,

     

    두번째 인수에 5는 –의 위치를 의미합니다. 1은 –뒤로 문자를 의미 하지만 저는 –만 바꾸고 싶으므로 1을 입력합니다. *는 숫자가 아닌 문자 이므로 반드시 "" 안에 들어가 주어야 합니다.

     

     

    명확 하죠?

     

    사실 저도 함수의 사용법을 다 외우느냐? 절대 아닙니다. 그러나 함수 이름은 알고 있고 사용법을 알고 있으면 함수를 입력하면 나오는 도움말을 참조 하면서 입력 합니다. 영어공부도 되니 실시간 도움말에 익숙해 집시다.

     

    좀 전의 5를 8로만 바꾸었습니다.

     

     

    8번째 위치에 자리한 –만 바뀌었습니다.

     

     

    REPLACE 가 유용할 경우는 항상 일정한 위치에 문자를 바꿀 때 이겠죠? 지금처럼 –나 특정한 문자를 바꿀 경우는 REPLACE함수 보다 SUBSTITUTE 함수가 유용합니다.

     

  2. SUBSTITUTE 함수

    만약 다이내믹한 데이터 변화를 원하는 것이 아니라면 SUBSTITUTE 함수는 거의 필요 없습니다. 왜냐 하면 엑셀의 기본 기능인 바꾸기 기능과 똑같거든요. 편하게 할 수 있는데 굳이 함수를 쓸 필요가 없죠. SUBSTITUE함수가 유용할 경우는 다이내믹한 데이터의 업데이트를 원할 때, 또는 바꾸는 문자의 개수를 제한 할 때 사용합니다.

    함수 사용법

    SUBSTITUTE(바꿀셀,대상문자,바꿀문자,바꿀문자위치순서)는 해당 위치에 순서에 있는 대상 문자를 바꿉니다. 만약 4번째 인수 바꿀 문자 개수가 입력되지 않으면 모든 문자를 바꿉니다.

     

    엑셀의 기본 기능인 바꾸기 와 SUBSTITUE은 거의 동일 합니다.

     

     

    대상 문자 인수에 "-" 가 들어 갔고 바꿀 문자는 "" 즉 공백입니다. –가 두개 있지만 첫번째 것만 제거 합니다.

     

     

    두번째 –는 살아 남았죠? 첫번째 – 만 제거 되었습니다.

     

     

    이번에는 두번째 –만 바꿔보겠습니다. 숫자 2가 인수로 들어간 것을 보세요.

     

     

    두번째 –만 ***로 바뀌었습니다.

     

     

    4번째 인수를 생략해 보겠습니다.

     

     

    모든 –가 *로 바뀌었습니다.

     

     

    4번째 인수를 생략하고 3번째 인수에 공백을 넣습니다.

     

     

    모든 –가 제거 되었습니다.

     

     

    모든 문자열을 바꾸는 것은 SUBSITUTE함수를 이용하는 것 보다 컨트롤 +F를 이용하는 것이 편합니다.

     

    해당 영역을 미리 선택하고 컨트롤 + F (F는 FIND의 F) 를 누릅니다. 찾을 내용에 -, 바꿀 내용을 비워두고 모두 바꾸기를 누릅니다.

     

     

    이 방법으로 특정 위치를 바꾸려면 노가다를 해야 하지만 모든 위치를 바꿀 때는 함수보다 편합니다.

    사실 엑셀의 주민번호, 전화번호 여러 서식 질문들을 보면 서식 와일드 카드를 몰라도 CONCATONATION SUBSTITUTE함수를 이용하면 대부분 간단히 해결 가능한 입니다. 모르면 3시간 알면 1분짜리 문제들이 많죠.


+ Recent posts