엑셀 SUBSTITUTE 함수 사용법에 관해 알아보는 글입니다. 문자열에서 문자를 새로운 문자로 바꾸는 경우 사용할 수 있는 함수입니다. 지금부터 SUBSTITUTE 함수 사용법에 관해 알아보겠습니다.
엑셀 연습 파일을 다운로드 받아서 연습을 해보면 이해가 훨씬 쉽습니다.
엑셀 SUBSTITUTE 함수 사용법
▶︎= SUBSTITUTE ( 문자열, 찾을문자, 새로운문자, [바꿀지점] )
▶︎모든 찾을문자를 새로운문자로 바꿔주는 함수
엑셀 SUBSTITUTE 함수는 문자열에서 찾을 문자를 새로운 문자로 바꿔주는 함수 입니다.
단, SUBSTITUTE 함수는 문자열에 있는 찾을 문자를 모두 다 바꿉니다. 때문에 특정 문자만 바꾸려는 경우에는 REPLACE 함수를 사용하는 것이 더 적합 합니다.
아래 사진처럼 1번부터 6번까지 각 결과 값을 반환 하는 방법을 차례대로 알아 보겠습니다.
①기본 사용법
- 문자열: 사과나무에 사과 있다.
- 수식 입력: =SUBSTITUTE(A1,”사과”,”호박”)
- 결과 값: 호박나무에 호박 있다
‘사과’라고 입력이 되어 있는 부분을 ‘호박’으로 바꿔보겠습니다. 그러기 위해 위와 같이 수식을 입력 합니다. substitute 함수 첫 번째 인수로 텍스트를 바꿀 문자열이 들어갑니다.
두 번째로 찾을 문자를 입력 해 주면 되구요. 세번째로 바꿀 문자를 입력 해주면 됩니다. 여기서 텍스트의 경우 반드시 큰 따옴표(“)를 입력 해 주셔야 합니다.
②바꿀지점 입력하기
- 문자열: 2023년 2/4분기
- 수식 입력: =SUBSTITUTE(A2,”2″,”1″,2)
- 결과 값: 2013년 2/4분기
▶︎= SUBSTITUTE ( 문자열, 찾을문자, 새로운문자, [바꿀지점] )
이번에는 Substitute 함수 4번째 인수인 바꿀 지점을 입력해 보겠습니다. 바꿀지점 인수의 경우 대괄호로 묶여 있습니다. 때문에 입력을 해도 되고 안해도 됩니다.
바꿀지점을 입력 하지 않으면 문자열의 전체 텍스트가 바뀌게 되구요. 바꿀지점을 입력 하면 내가 입력한 번호에 해당되는 텍스트만 바뀌게 됩니다.
- 문자열: 2023년 2/4분기
위 문자열을 보시면 2라는 숫자가 총 3개가 들어가 있습니다. 여기서 두 번째에 해당 되는 2을 1로 바꿔보도록 하겠습니다. 그러기 위해서 아래와 같이 수식을 입력해 주세요.
=SUBSTITUTE(A2,”2″,”1″,2)
그럼 두 번째에 해당되는 2만 1로 바뀌게 되는 것을 확인할 수 있습니다.
2023년 2/4분기 → 2013년 2/4분기
③바꿀지점 입력하기
- 문자열: 2023년 2/4분기
- 수식 입력: =SUBSTITUTE(A2,”2″,”1″,3)
- 결과 값: 2023년 1/4분기
이번에도 같은 방식인데요. 이번에는 두 번째 2가 아니라 세 번째 2을 1로 바꿔보겠습니다. 그러기 위해서 위와 같이 수식을 입력 합니다. 그럼 세 번째 해당 되는 2가 1로 바뀌는 것을 알 수 있습니다.
④바꿀지점 값 찾을문자 개수보다적어야 함
substitute 함수를 입력 할 때 주의해야 할 점이 있습니다. substitute 함수 내 4번째 인수인 바꿀지점 값은 찾을 문자의 개수보다 더 적게 입력을 해야 한다는 것입니다.
▶︎= SUBSTITUTE ( 문자열, 찾을문자, 새로운문자, [바꿀지점] )
예를 들어 아래와 같은 예시의 경우 2라는 숫자가 문자열에 총 3개가 나와 있는데요. 바꿀지점을 4로 입력을 하면 값이 나오지 않습니다. 따라서 반드시 바꿀지점의 값을 찾을 문자 개수보다 적게 입력 해야 합니다.
▶︎2023년 2/4분기 → ‘2’ 라는 숫자가 총 3개 있음. → 바꿀지점 값으로 1,2,3 중에 입력을 해야 함.
⑤대소문자 구분
SUBSTITUTE 함수는 대소문자를 구분 합니다. 따라서 함수 내 인수를 입력할 때 반드시 대소문자를 정확하게 입력 해 주셔야 합니다.
- 문자열: LG 노트북 128GB
- 수식: =SUBSTITUTE(A4,”lg“,”삼성”)
- 결과 값: LG 노트북 128GB
위의 예시를 보시면 문자열에는 대문자로 LG 가 입력이 되어 있는데 수식에는 소문자로 lg 를 입력 했습니다. 그에 대한 결과 값을 보시면 LG 에서 삼성으로 텍스트가 바뀌지 않은 것을 알 수 있습니다.
이는 Substitute 함수가 대소문자를 구분 하기 때문입니다
⑥하이픈(-) 없애기
- 문자열: 010-1234-5678
- 수식: =SUBSTITUTE(A7,”-“,””)
- 결과 값: 01012345678
substitute 함수를 실무에서 유용하게 활용 하는 한가지 방법을 알아 보겠습니다. 예를 들어서 전화번호나 날짜에 하이픈(-)이 입력 되어 있는 경우가 있습니다. 이러한 경우 하이픈(-)을 없애고 싶을 수 있는데요.
SUBSTITUTE 함수를 이용해서 하이픈(-)을 없앨 수 있습니다. 하이픈 부분을 공백으로 바꾸면 됩니다.
=SUBSTITUTE(A7,”-“,””)
따라서 위와 같이 수식을 입력 하고 엔터키를 누르면 하이픈이 없어진 것을 확인 할 수 있습니다.
한편 010 1234 5678 이라고 표시가 되게 하고 싶을 수 있는데요. 그러기 위해선 =SUBSTITUTE(A7,”-“,” “) 처럼 세 번째 인수 부분의 큰 따옴표 안에서 스페이스 바를 한 번 눌러서 공백을 주면 됩니다.
<결론>
▶︎엑셀 SUBSTITUTE 함수 사용법
= SUBSTITUTE ( 문자열, 찾을문자, 새로운문자, [바꿀지점] )
- 모든 찾을문자를 새로운문자로 바꿔주는 함수
- 바꿀 지점 값이 찾을문자 개수보다 적어야 함
- 바꿀 지점은 입력을 해도 되고, 안 해도 됨
- SUBSTITUTE 함수는 대소문자 구분함
이상 엑셀 SUBSTITUTE 함수 사용법에 관한 글이었습니다.