[엑셀 함수] 엑셀의 REPLACE , SUBSTITUTE 함수 - 문자열함수

아래의 게시물은 이전에 직접 운영하였던 컴퓨터활용능력과 엑셀 관련 사이트에 게시한 컴퓨터활용능력 1급 함수 중 문자열함수에 속하는 REPLACE 함수와 SUBSTITUTE 함수에 대한 설명입니다. 이 블로그에도 컴퓨터활용능력 1급 부분에 올라갈 함수들입니다. 미리 작업한 것이 있기에 올립니다. 도움되시길 바랍니다.

안녕하세요. 이번 게시물 에서는 컴퓨터활용능력 1급 실기에 사용되는 엑셀의 문자열 함수를 다뤄봅니다. 컴활1급 실기에 사용되는 함수들이라고 별다르게 어려울것은 없습니다.
먼저 컴활1급에 사용되는 엑셀 문자열 함수 REPLACE 입니다. REPLACE 란 함수명에서 벌써 감이 오시지요. 네 그렇습니다. 엑셀에서 텍스트의 교체를 위한 함수입니다. 오늘 배우실 두 엑셀 함수 모두 텍스트 문자열의 일부를 원하는 다른 문자들로 교체할 때 사용하는 함수입니다. 두 엑셀 함수 중 상황에 맞는 교체함수를 사용하시면 되겠습니다. 문자열 교체함수는 REPLACE와 SUBSTITUTE 두 함수가 있다는 것을 기억하세요. 그럼 먼저 엑셀의 REPLACE 함수부터 시작합니다.

* 엑셀 REPLACE 함수

기능: 전체 문자열 중에 원하는 부분을 다른 문자들로 바꿔주는 엑셀 함수입니다.
용법: =REPLACE( 원본 문자열, 교체 시작문자 위치, 교체할 문자수, 교체 문자열 )
    - '원본 문자열' 중에서 '교체 시작문자 위치' 부터 '교체할 문자수' 만큼의 문자를 '교체 문자열' 로 바꿔줍니다.

[그림1] 엑셀 REPLACE 함수 사용 예제

용례1: =REPLACE( "컴활1급탈락", 5, 2, "합격" )
용례1의 결과: 컴활1급합격
( "컴활1급탈락" 문자열에서 5번째 문자부터 2자, "탈락" 을 "합격" 으로 교체합니다. 여기서 5와 2는 바이트수가 아닙니다. 단순 문자수임을 기억하세요. 한글과 영문 모두 1자로 취급한다는 것입니다.)

용례2: =REPLACE( "컴활1급불합격", 5, 3, "합격" )
용례2의 결과: 컴활1급합격
( 위의 용례1과 교체할 문자의 수가 다릅니다. 하지만 교체될 문자의 수는 같지요. 즉 3자를 2자로도 교체할수 있다는 것입니다. 2번째인수의 교체시작문자 부터 3번째 인수인 교체할 문자수 까지의 문자들, 즉 교체할 문자수 와 교체될 문자수가 달라도 교체 가능하다는 것을 기억해 주세요. )

용례3: =REPLACE( A1, 3, 1, "1" )
용례3의 결과: 컴활1급실기
( A1 셀에 문자열 "컴활3급실기" 가 입력된 상태입니다. 그렇다면 3번째부터 1문자인 "3" 이 "1"로 교체됩니다. A1 셀에 만일 "컴활2급실기" 가 입력된 상태라도 결과는 동일합니다. )

용례4: =REPLACE( "우리나라대한민국", 4, 1, "라   " )
용례4의 결과: 우리나라   대한민국
( 원본문자열 "우리나라대한민국" 의 중간을 띄우고 싶습니다. 이때도 이 함수를 사용하면 쉽습니다. 4번째문자 1문자 "라" 를 "라   " 로 바꾸면 "우리나라" 와 "대한민국" 사이에 공백을 추가할 수 도 있지요. 반대로 텍스트 사이의 공백을 제거할 때도 사용할 수 있습니다.)

* 엑셀 SUBSTITUTE 함수

기능: 전체 문자열 중 원하는 문자열을 다른 문자열로 교체해 주는 엑셀 함수입니다. 엑셀 REPLACE 함수가 문자열에서 '교체 시작위치'와 '교체 문자수'를 인수로 원한다면 이 함수는 '교체할 문자열' 자체를 인수로 요구합니다.
용법: =SUBSTITUTE( 원본 문자열, 교체할 문자열, 교체될 문자열 )
  - 원본 문자열 중에서 교체할 문자열을 교체될 문자열로 교체해 줍니다.

[그림2] 엑셀 SUBSTITUTE 함수 사용 예제

용례1: =SUBSTITUTE( "컴활1급불합격", "불합격", "합격" )
용례1의 결과: 컴활1급합격
( "컴활1급불합격" 의 텍스트 중에서 "불합격" 의 텍스트를 "합격" 으로 교체하여 줍니다. REPLACE 함수 의 용례2와 같은 예제인데 어찌보면 간단히 해결되었지요? )

용례2: =SUBSTITUTE( "WIN 98", "98", "XP" )
용례2의 결과: WIN XP
( "WIN 98" 의 텍스트에서 "98" 의 텍스트 "XP" 로 교체합니다.)

엑셀 REPLACE함수와 엑셀 SUBSTITUTE 함수 비교 예제

아래의 J열의 '기술등급' 중 '초급'을 '중급'으로 변환해 봅니다.

[그림3] 엑셀 REPLACE , SUBSTITUTE 함수 비교 예제

L열에서 REPLACE 함수를 이용하여 교체하였습니다. 그런데 '고급'까지 '중급'으로 교체되었지요. 이유가 뭘까요? 네 그렇습니다. REPLACE함수는 교체를 원하는 문자열의 위치와 갯수를 필요로 합니다. 하여 동일위치에 있는 "초급","고급" 문자열이 모두 "중급"으로 교체된 것입니다. L8의 수식은 이렇습니다.

=REPLACE( J8, 1, 2, "중급" )

J8 셀의 문자열중 1번째 문자부터 2자를 "중급" 으로 교체하는 것이지요. 이 수식을 L15까지 채우기핸들로 수식복사하였더니 위와 같은 결과를 얻은것입니다.

M열은 두 번째 문자열 교체 함수, 엑셀 SUBSTITUTE 함수를 이용하여 작업한 것입니다. M8 셀의 수식은 이렇습니다.

=SUBSTITUTE( J8, "초급", "중급" )

J8 셀의 문자열 중 "초급" 을 찾아 이를 "중급"으로 교체하여 줍니다. 만일 "초급" 이 없다면 교체할 문자가 없기에 교체작업은 일어나지 않습니다. 그래서 "고급기술자" 는 그대로 표시되는 것입니다.

이상이 엑셀 문자열 교체 함수, REPLACE 함수와 SUBSTITUTE 함수입니다.

컴활1급에 사용되는 엑셀 문자열 함수들입니다. REPLACE 함수가 우리가 편집기들에서 흔히 보는 REPLACE 도구(찾아바꾸기)의 방법을 따르는 함수가 아니라는 것 기억하세요. 찾아바꾸기 기능을 하는 함수는 엑셀의 SUBSTITUTE 함수입니다.

마늘빵.