Blog

엑셀 SUBSTITUTE 함수와 REPLACE 함수의 이해 및 활용

엑셀에서는 텍스트의 일부를 변경하거나 대체하는 작업을 간편하게 수행할 수 있는 여러 함수가 있습니다. 그중에서 특히 많이 사용되는 두 가지 함수가 바로 SUBSTITUTE와 REPLACE입니다. 이 두 함수는 비슷한 역할을 하지만, 각기 다른 상황에서 사용됩니다. 이번 포스팅에서는 이 두 함수의 차이점과 사용 방법에 대해 상세히 알아보도록 하겠습니다.

SUBSTITUTE 함수란?

SUBSTITUTE 함수는 특정 텍스트 문자열 내에서 기존 문자를 새로운 문자로 바꿔주는 역할을 합니다. 이 함수는 사용자가 지정한 모든 인스턴스를 대체하거나, 특정 인스턴스만을 선택적으로 대체할 수 있는 유연성을 제공합니다. SUBSTITUTE 함수의 기본 구문은 다음과 같습니다:

• =SUBSTITUTE(text, old_text, new_text, [instance_num])

여기서

• text: 수정하고자 하는 문자열이 들어 있는 셀을 지정합니다.
• old_text: 교체할 기존 텍스트입니다.
• new_text: 새롭게 삽입할 텍스트입니다.
• instance_num: (선택적) 변경할 몇 번째 인스턴스를 지정합니다. 생략할 경우 모든 인스턴스가 변경됩니다.

예를 들어, 문자열 “안녕하세요. 안녕!”에서 “안녕”을 “안녕하세요”로 바꾸고 싶을 때 다음과 같이 사용할 수 있습니다:

• =SUBSTITUTE(“안녕하세요. 안녕!”, “안녕”, “안녕하세요”) ➔ ‘안녕하세요. 안녕하세요!’

REPLACE 함수란?

REPLACE 함수는 문자열에서 특정 위치에 있는 문자를 다른 문자로 대체하는 데 사용됩니다. 기존 문자열의 일부를 지정된 위치와 길이에 따라 새로운 문자열로 교체하는 방식입니다. REPLACE 함수의 기본 구문은 다음과 같습니다:

• =REPLACE(old_text, start_num, num_chars, new_text)

여기서

• old_text: 변환하고자 하는 문자열입니다.
• start_num: 교체를 시작할 위치입니다. 문자열의 첫 글자는 1로 간주됩니다.
• num_chars: 교체할 텍스트의 길이입니다.
• new_text: 바꿀 새로운 텍스트입니다.

예를 들어, “2023 프로젝트”의 “2023”을 “2024”로 바꾸고 싶다면 다음과 같은 수식을 사용할 수 있습니다:

• =REPLACE(“2023 프로젝트”, 1, 4, “2024”) ➔ ‘2024 프로젝트’

SUBSTITUTE와 REPLACE의 주요 차이점

두 함수 모두 텍스트를 수정하는 기능을 제공하지만, 각 함수의 사용 시나리오는 확연히 다릅니다.

• SUBSTITUTE: 문자열 내의 특정 문자나 단어를 모두 또는 선택적으로 바꿀 때 사용합니다. 위치와 관계없이 변경이 가능하므로, 동일한 문자열이 여러 번 등장할 경우 유용합니다.
• REPLACE: 문자의 위치와 길이를 기반으로 변환합니다. 따라서 특정 위치에 있는 문자를 정확하게 수정하고자 할 때 적합합니다.

각 함수의 활용 방법

SUBSTITUTE 함수의 활용 사례

SUBSTITUTE 함수는 특히 데이터 클리닝이나 문자열 치환 작업에서 유용합니다. 예를 들어, 여러 고객의 연락처에서 하이픈(-)을 모두 제거하고자 할 때 다음과 같이 사용할 수 있습니다:

• =SUBSTITUTE(A1, “-“, “”) ➔ 하이픈이 제거됩니다.

REPLACE 함수의 활용 사례

REPLACE 함수는 기밀 정보를 보호하기 위해 특정 부분을 마스킹하는 데 자주 사용됩니다. 예를 들어, 주민등록번호의 뒷자리를 ‘*’로 바꾸고 싶을 때는 다음과 같이 사용할 수 있습니다:

• =REPLACE(“**567”, 8, 7, “*******”) ➔ ‘123456-*******’

함수 사용 시 주의사항

SUBSTITUTE와 REPLACE 함수를 사용할 때는 다음과 같은 점에 유의해야 합니다:

• SUBSTITUTE 함수는 대소문자를 구분하여 작동하므로, 정확한 문자를 입력해야 합니다.
• REPLACE 함수에서 start_num이나 num_chars가 문자열의 범위를 초과하는 경우 오류가 발생할 수 있습니다.
• REPLACE 함수는 음수 값을 시작 위치나 문자 길이로 지정할 수 없습니다.

이와 함께 공백이나 기타 불필요한 문자를 처리할 때도 각 함수를 활용할 수 있으니, 데이터 처리 과정에서 적절한 함수를 선택하여 사용하면 좋겠습니다.

결론

SUBSTITUTE와 REPLACE 함수는 엑셀에서 문자열을 다룰 때 필수적인 도구입니다. 각 함수의 특성과 사용법, 활용 사례를 이해하고 나면 더 효과적으로 데이터를 관리하고 분석할 수 있을 것입니다. 필요에 따라 적절한 함수를 선택하여 데이터 처리의 효율성을 높여보세요.

자주 찾으시는 질문 FAQ