카테고리 없음

엑셀(Excel) 함수 정리 PROPER, REPLACE, SUBSTITUTE

clfl72 2025. 12. 18.

엑셀에서 텍스트 데이터를 다룰 때 자주 접하는 문제 중 하나는 잘못된 대소문자 표기, 특정 문자나 단어의 반복, 포맷 오류입니다. 이럴 때 PROPER, REPLACE, SUBSTITUTE 함수를 사용하면 깔끔하고 정확한 데이터 정리가 가능합니다. PROPER는 단어의 첫 글자를 대문자로 바꾸는 데, REPLACE는 특정 위치의 문자를 교체할 때, SUBSTITUTE는 특정 단어나 문자를 전체적으로 바꿀 때 매우 유용하게 쓰입니다. 고객명 정리, 문서 포맷 통일, 개인정보 마스킹 등 다양한 실무 작업에서 반드시 알아두어야 할 필수 텍스트 처리 함수입니다.

PROPER

PROPER 함수는 문자열에서 각 단어의 **첫 글자만 대문자**로 변환하고 나머지는 모두 소문자로 만드는 함수입니다. 구문은 =PROPER(텍스트)이며, 이름, 도시명, 제목 등 고유명사를 표준화할 때 매우 유용합니다. 특히 사용자 입력이 모두 대문자거나 소문자로 되어 있는 경우에도 일관성 있는 형식으로 변환할 수 있어 실무에서 자주 활용됩니다.

예제 1: 사람 이름 형식화

=PROPER("lee ji hoon") 수식은 "Lee Ji Hoon"이라는 결과를 반환합니다. 고객 관리 시스템이나 명부 정리 시 이름을 보기 좋게 출력할 수 있어 인사 기록, 이메일 리스트, 설문 응답 데이터 정리에 적합합니다.

예제 2: 주소나 국가명 정리

=PROPER("republic of korea")를 사용하면 "Republic Of Korea"로 변환됩니다. 지명이나 국가명을 일정한 형식으로 출력할 수 있어 통계 분석이나 보고서 작성 시 데이터 일관성을 유지할 수 있습니다.

예제 3: 혼합 텍스트 자동 정리

=PROPER(A1)로 "SEOUL national UNIVersity"를 변환하면 "Seoul National University"가 됩니다. 입력자의 실수나 복붙된 데이터를 자동으로 보정하는 데 매우 유용하며, 데이터베이스 입력 전 정리용으로도 자주 쓰입니다.

REPLACE

REPLACE 함수는 문자열 내 **특정 위치**에 있는 문자를 사용자가 지정한 값으로 교체하는 함수입니다. 구문은 =REPLACE(원본텍스트, 시작위치, 바꿀문자수, "새문자열")이며, 정형화된 데이터를 수정하거나 고정된 위치의 정보를 갱신하는 데 적합합니다. 주민번호, 전화번호, 제품코드 등 위치가 고정된 문자열의 일부를 바꾸고자 할 때 효과적입니다.

예제 1: 주민번호 마스킹

=REPLACE("900101-1234567", 8, 7, "*******")는 "900101-*******"을 반환합니다. 개인정보 보호를 위해 주민등록번호의 뒷자리를 마스킹하는 데 자주 사용되며, 자동화된 개인정보 처리 문서에서 널리 활용됩니다.

예제 2: 전화번호 중간 번호 수정

=REPLACE("010-1234-5678", 5, 4, "0000")는 "010-0000-5678"로 출력됩니다. 이처럼 전화번호, 카드번호, 계좌번호 등 포맷이 일정한 데이터의 일부분만 바꾸는 데 매우 적합하며, 정기적인 데이터 갱신 작업에서 활용도가 높습니다.

예제 3: 연도 변경 자동화

=REPLACE("ITEM2023", 5, 4, "2024")는 "ITEM2024"를 반환합니다. 코드나 명칭에 포함된 연도 정보를 자동으로 갱신하거나 반복 작업을 줄일 수 있어 연간 단위 보고서나 재고 관리에 매우 유용합니다.

SUBSTITUTE

SUBSTITUTE 함수는 문자열에서 **특정 텍스트를 찾아 다른 텍스트로 바꾸는 함수**입니다. REPLACE와 달리 위치가 아니라 **단어 또는 문자열** 기준으로 작동하며, 전체 또는 지정한 횟수만큼 치환할 수 있습니다. 구문은 =SUBSTITUTE(텍스트, 기존텍스트, 새텍스트, [바꿀횟수]) 형식입니다. 일괄적인 문자열 교체 작업이나, 문자 정제, 단어 변경 등에 탁월한 효과를 발휘합니다.

예제 1: 특정 단어 변경

=SUBSTITUTE("서울본사, 서울지점", "서울", "부산") 수식은 "부산본사, 부산지점"을 반환합니다. 동일한 텍스트가 여러 번 나타나도 전부 바뀌며, 도시명 변경, 회사명 수정, 브랜드명 리브랜딩 등 반복적인 텍스트 수정이 필요한 상황에서 매우 유용합니다.

예제 2: 특수문자 제거

=SUBSTITUTE(A1, "-", "")를 사용하면 하이픈(-)을 모두 제거할 수 있습니다. 예: "010-1234-5678" → "01012345678". 전화번호, 우편번호, 사업자번호 등에서 특수문자 제거가 필요한 경우 매우 간편하게 처리할 수 있습니다.

예제 3: 특정 횟수만 치환

=SUBSTITUTE("aaa-bbb-ccc", "-", "/", 2)는 두 번째 하이픈만 "/"로 바꿔 "aaa-bbb/ccc"를 반환합니다. 특정 위치의 문자만 바꾸고 싶을 때 REPLACE보다 유연하게 사용할 수 있으며, 구분자 조정이나 일부 치환이 필요한 데이터 가공에 유용합니다.