- TEXTSPLIT으로도 못 자르는 지저분한 텍스트의 종결자: 구분 기호(쉼표·하이픈) 위치가 제각각인 상담 메모·입금 내역에서 패턴(숫자 몇 자리, 이메일 형식 등)만으로 데이터를 검증·추출·치환하는 Microsoft 365 신규 PCRE2 정규식 함수 3대장입니다.
- 3대 함수 핵심 분담: 형식 적합성 TRUE/FALSE 판별은
REGEXTEST, 주문번호·금액·캡처 그룹 자동 추출은REGEXEXTRACT, 주민번호·휴대폰 개인정보 마스킹(비식별화) 및 불필요 문자 일괄 제거는REGEXREPLACE가 담당합니다. - 엑셀 버벅임 없애는
TRIMRANGE& 트림 참조(.:.):A2:.A5000처럼 콜론 옆에 마침표(.) 하나만 찍어주면 데이터가 입력된 마지막 행까지만 동적으로 범위를 잘라주어 정규식 수식 연산 속도가 비약적으로 빨라집니다. - 실무 복붙 즉시 활용: 한국 직장인이 가장 자주 쓰는 6대 정규식 패턴(휴대폰 번호, 주민번호 뒷자리 마스킹, 이메일, 사업자번호, 괄호 안 텍스트, 천 단위 원화 금액 추출)을 표와 수식 블록으로 바로 복사해 쓸 수 있게 정리했습니다.
엑셀 실무에서 가장 많은 야근을 유발하는 데이터는 깔끔하게 열이 나뉘어 있는 표가 아닙니다. 쇼핑몰 고객센터 상담원이나 은행 입출금 비고란에 사람이 직접 타이핑해 넣은 “홍길동님 11/15 주문건(ORD-2026-8841) 환불요청 45,000원 연락처 010-1234-5678”처럼 규칙도 없고 어순도 매번 바뀌는 비정형 텍스트입니다. 기존에는 이런 문자열에서 연락처나 금액만 뽑아내려고 MID, FIND, SUBSTITUTE, LEN 함수를 다섯 겹씩 중첩하다가 위치가 한 칸만 어긋나도 #VALUE! 오류 폭탄을 맞곤 했습니다.
물론 구분자가 일정할 때는 앞서 소개해 드린 TEXTSPLIT · TEXTBEFORE · TEXTAFTER 함수로 주소·상품코드 1초 자동 분리하는 법만으로도 훌륭하게 해결됩니다. 하지만 구분 기호 자체가 없거나 전화번호에 하이픈(-)을 넣은 사람과 안 넣은 사람이 섞여 있을 때는 결국 ‘정규표현식(Regular Expression, Regex)’이 유일한 해답입니다. 과거에는 VBA 매크로로 VBScript.RegExp 객체를 불러와야만 쓸 수 있었던 정규식이 이제 Microsoft 365 엑셀 기본 워크시트 함수 REGEXTEST, REGEXEXTRACT, REGEXREPLACE 3총사로 정식 탑재되었습니다!
이번 엑셀 실무 팁에서는 정규식 문법을 전혀 모르는 입문자도 5분 만에 복사해 현업 시트에 바로 꽂아 쓸 수 있는 3대 REGEX 함수의 정확한 인수 구조, 한국 실무 맞춤형 6대 마스터 패턴 표, 개인정보 비식별화(마스킹) 실전 예제, 그리고 수만 행 배열 수식의 속도 저하를 단번에 잡는 최신 TRIMRANGE(.:.) 결합 테크닉까지 완벽히 마스터해 보겠습니다.
1. 엑셀 정규식 함수 3대장(REGEXTEST · EXTRACT · REPLACE) 공식 구문과 역할 비교
Microsoft 365 엑셀의 정규식 함수는 업계 표준인 PCRE2(Perl Compatible Regular Expressions 2) 엔진을 기반으로 동작합니다. 세 함수 모두 기본적으로 영문 대소문자를 구분(case_sensitivity = 0)하며, 마지막 인수에 1을 넣으면 대소문자 구분 없이 매칭합니다.
| 함수명 및 공식 구문 | 반환값 형태 | 핵심 옵션 인수 (실무 포인트) | 대표 실무 활용처 |
|---|---|---|---|
=REGEXTEST(text, pattern, [case_sensitivity]) |
논리값 (TRUE / FALSE) |
0(기본): 대소문자 구분1: 대소문자 무시 |
이메일·전화번호·사번 오타 검증, FILTER 함수 조건식 결합 |
=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity]) |
텍스트 단일값 또는 가로 배열 스필(Spill) | return_mode (핵심!)0(기본): 첫 번째 일치 항목만 반환1: 일치하는 모든 항목을 배열로 반환2: 첫 일치 항목 내 () 캡처 그룹들을 각각의 셀로 분리 반환 |
비정형 메모에서 주문번호·금액·날짜 추출, 코드 3단 분해 |
=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity]) |
치환된 텍스트 문자열 | occurrence: 0(기본, 전체 치환), 양수 N(앞에서 N번째만), 음수 -1(뒤에서 첫 번째만 치환!) + $1, $2 역참조 지원 |
주민등록번호·연락처 가운데 자리 **** 마스킹, 숫자 외 한글·기호 일괄 삭제 |
1-1. 외울 필요 없는 한국 직장인 필수 정규식 기호 6개 요약
정규식이 암호처럼 보여 겁먹을 필요가 전혀 없습니다. 실무의 95%는 딱 여섯 가지 기호 조합으로 끝납니다. ① [0-9] 또는 \d(숫자 1글자, 반각 아라비아 숫자만 엄격히 잡을 땐 [0-9] 권장), ② +(앞 패턴이 1번 이상 연속됨), ③ {n,m}(앞 패턴이 최소 n자리~최대 m자리 반복), ④ ?(앞 문자가 있어도 되고 없어도 됨, 예: 하이픈 생략 대응 -?), ⑤ ^와 $(각각 문자열의 맨 처음과 맨 끝), ⑥ ( )(원하는 조각만 묶어내는 캡처 그룹, 치환 시 $1, $2로 재호출)입니다.
2. 복사해서 바로 쓰는 한국 실무 6대 정규식 레시피 (검증·추출·마스킹)
인사팀, 재무팀, CS·물류팀에서 매일 마주치는 한국형 데이터 정제 문제를 해결하는 완성형 수식 표입니다. 대상 텍스트가 A2 셀에 있다고 가정하고 그대로 복사해 붙여넣으세요.
| 실무 시나리오 (A2 원본 예시) | 바로 복사해 쓰는 완성 수식 | 출력 결과 및 작동 원리 |
|---|---|---|
① 휴대폰 번호 포맷 통일01012345678 또는 010 1234 5678 |
=REGEXREPLACE(A2, "(01[016789])[- ]?([0-9]{3,4})[- ]?([0-9]{4})", "$1-$2-$3") |
010-1234-5678하이픈·공백 유무와 무관하게 3그룹 캡처 후 표준 하이픈 자동 삽입 |
② 휴대폰 번호 가운데 마스킹담당자: 010-9876-5432 |
=REGEXREPLACE(A2, "(01[016789])-?[0-9]{3,4}-?([0-9]{4})", "$1-****-$2") |
담당자: 010-****-5432개인정보보호법 준수 고객 명단 배포 시 1초 비식별화 |
③ 주민등록번호 뒷자리 마스킹920815-1234567 |
=REGEXREPLACE(A2, "([0-9]{6}-[1-4])[0-9]{6}", "$1******") |
920815-1******생년월일과 성별코드 첫 자리만 남기고 뒤 6자리 별표 처리 |
④ 메모 속 원화 결제금액만 숫자로 추출입금확인 1,250,000원 완료 |
=--SUBSTITUTE(REGEXEXTRACT(A2, "[0-9,]+(?=원)"), ",", "") |
1250000 (계산 가능한 숫자!)‘원’ 글자 바로 앞(긍정형 전방탐색) 숫자·쉼표만 뽑아 수치 변환 |
⑤ 주문코드 3단 캡처 그룹 자동 분해결제건 ORD-2026-0492 접수 |
=REGEXEXTRACT(A2, "([A-Z]{3})-([0-9]{4})-([0-9]{4})", 2) |
옆 셀 3칸으로 ORD | 2026 | 0492 자동 스필!( return_mode = 2의 강력한 위력) |
⑥ 이메일 주소 형식 오타 검증kim.smart@soongin.com |
=REGEXTEST(A2, "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$") |
TRUE (골뱅이나 도메인 누락 시 FALSE 반환) |
#N/A 오류가 뜰 때 깔끔하게 막는 법: 상담 메모 중 전화번호가 아예 적히지 않은 행이 섞여 있으면 REGEXEXTRACT는 #N/A를 반환합니다. 이때는 바깥을 =IFNA(REGEXEXTRACT(A2, "01[0-9]-?[0-9]{3,4}-?[0-9]{4}"), "연락처 미기재")처럼 IFNA 함수로 한 번만 감싸주면 보고서 표가 단정하게 유지됩니다!
3. REGEXTEST × FILTER 결합 & 최신 트림 참조(.:.)로 대용량 속도 10배 높이기
정규식 함수가 진짜 빛을 발하는 순간은 동적 배열 함수들과 결합할 때입니다. 앞서 다룬 FILTER · UNIQUE · SORT 함수 3대장 실시간 자동 갱신 보고서 만드는 법에서는 단순 일치 조건(=)만 걸 수 있었지만, 이제 FILTER의 두 번째 인수에 REGEXTEST를 넣으면 SQL 데이터베이스의 WHERE ... REGEXP 쿼리처럼 다중 키워드 부분 검색이 가능해집니다!
3-1. ‘환불’ 또는 ‘파손’ 또는 ‘오배송’이 포함된 CS 행만 실시간 자동 추출하기
기존 엑셀에서 3개 단어가 하나라도 포함된 행을 필터링하려면 ISNUMBER(SEARCH(...))+ISNUMBER(SEARCH(...))처럼 끔찍하게 긴 수식을 써야 했습니다. 정규식의 파이프(|, OR 연산자)를 쓰면 단 한 줄로 끝납니다. 게다가 어제 배운 LET · LAMBDA · SCAN 함수로 나만의 한글 함수 압축하는 법과 최신 트림 참조 연산자(:. / TRIMRANGE)를 함께 결합하면 원본 행이 매일 늘어나도 수식을 고칠 필요가 전혀 없습니다.
=LET(
전체범위, A2:.D50000,
상담메모열, INDEX(전체범위, 0, 4),
긴급조건, REGEXTEST(상담메모열, “환불|파손|오배송|누락”),
FILTER(전체범위, 긴급조건, “긴급 클레임 건 없음”)
)
▶ 핵심 포인트 해설:
1. A2:.D50000 (콜론 오른쪽에 마침표 . 추가 = 후행 빈 행 자동 제거!):
– 데이터가 현재 350행까지만 입력되어 있다면 엑셀이 알아서 범위를 A2:D350으로 즉시 잘라냅니다(TRIMRANGE 효과).
– 나머지 49,650개의 빈 행에 대해 무거운 정규식 연산을 수행하지 않아 파일 버벅임이 0초로 사라집니다!
– 내일 데이터가 420행으로 늘어나면 수식 수정 없이 자동으로 A2:D420까지 확장됩니다.
2. “환불|파손|오배송|누락”:
– 네 단어 중 단 하나라도 포함된 모든 클레임 행을 1초 만에 별도 대시보드 표로 스필(Spill) 추출합니다.
3-2. TRIMRANGE 함수와 점(.:.) 연산자 표기법 3가지 완벽 구분
Microsoft 365에 도입된 범위 트리밍 문법은 콜론(:)의 어느 쪽에 마침표(.)를 찍느냐에 따라 동작이 정교하게 나뉩니다.
A2:.D5000(콜론 우측 점 = 후행 공백 제거, 실무 활용도 1위!): 위쪽 시작 셀(A2)은 고정하고 아래쪽 끝의 빈 행들만 자동으로 잘라냅니다.=TRIMRANGE(A2:D5000, 2)와 완전히 동일합니다.A1.:D5000(콜론 좌측 점 = 선행 공백 제거): 표 상단에 빈 줄이 몇 줄 있는지 모를 때 앞쪽 빈 행만 제거합니다.A.:.D(양쪽 점 = 앞뒤 빈 행 모두 제거): 열 전체(104만 행)를 지정해도 실제 값이 시작되는 첫 행부터 마지막 행까지만 똑똑하게 잘라 가져옵니다.
4. 자주 묻는 질문 (FAQ Top 4) 및 데이터 정제 체크리스트
=REGEXEXTRACT를 쳤더니 #NAME? 오류가 뜹니다. 왜 그런가요?REGEXTEST, REGEXEXTRACT, REGEXREPLACE 및 TRIMRANGE 함수는 Microsoft 365 구독형 엑셀(Windows·Mac 최신 채널) 및 웹용 엑셀(Excel for the web) 전용 최신 함수입니다. 사내 PC가 엑셀 2016, 2019, 2021 같은 구형 영구 설치 버전이라면 무료 마이크로소프트 계정으로 브라우저에서 웹용 엑셀(excel.new)을 열어 대량 정제 후 값 붙여넣기로 내려받는 우회 팁을 추천합니다.
d와 [0-9]는 둘 다 숫자를 찾는다는데 실무에서 무슨 차이가 있나요?매우 중요한 디테일입니다! 엑셀 PCRE2 엔진에서 d는 유니코드 숫자 전반을 매칭하므로 전각 숫자(１２３４５ 등 특수문자형 숫자)까지 숫자로 인식해 통과시킬 수 있습니다. 반면 ERP 업로드용 계좌번호나 사업자번호처럼 순수 반각 아라비아 숫자(0~9)만 엄격히 걸러내야 할 때는 반드시 [0-9]를 사용해야 전산 업로드 오류를 막을 수 있습니다.
REGEXEXTRACT로 추출한 숫자로 SUM 합계를 구했더니 0이 나옵니다!REGEXEXTRACT와 REGEXREPLACE의 반환값은 항상 ‘텍스트(문자열)’ 자료형입니다. 따라서 숫자만 뽑아낸 뒤 합계나 크기 비교에 쓰려면 수식 맨 앞에 이중 마이너스 부호(=--REGEXEXTRACT(A2, "[0-9]+"))를 붙이거나 VALUE() 함수로 감싸 진짜 수치형 데이터로 변환해 주어야 정상 합산됩니다.
.)나 괄호(()), 달러($) 문자 그 자체를 찾고 싶을 때는 어떻게 하나요?정규식에서 . + * ? ^ $ ( ) [ ] { } | 기호는 특별한 명령어로 예약된 메타 문자입니다. 만약 이메일 도메인의 실제 마침표나 금액의 괄호 기호 자체를 글자로 찾고 싶다면 반드시 앞에 역슬래시(원화 기호 )를 붙여 ., (, )처럼 이스케이프(Escape) 처리해 주어야 오작동하지 않습니다.
- [ ] 외부 공유용 고객·인사 시트 배포 전
REGEXREPLACE로 주민등록번호 뒷자리($1******)와 연락처 가운데 자리(****) 비식별화 마스킹 적용하기 - [ ] 숫자 검증 시 전각 문자 혼입 방지를 위해
d대신 엄격한 반각 패턴인[0-9]사용하기 - [ ] 추출된 금액·수량 문자열을 계산에 쓸 때는 수식 앞에
--(이중 음수 연산자)를 붙여 숫자 형식으로 즉시 변환하기 - [ ] 추출 대상이 없는 빈 메모 행에서
#N/A가 뜨지 않도록IFNA(수식, "")으로 깔끔하게 예외 처리하기 - [ ] 동적 배열 수식에 전체 열(
A:D) 대신A2:.D50000트림 참조 연산자를 적용해 시트 재계산 속도 극대화하기
엑셀에서 정제·마스킹을 마친 데이터를 노션·슬랙·구글 시트로 24시간 자동 전송하는 다음 단계가 궁금하시다면 오늘 함께 발행된 클로드 MCP(커넥터) × 노코드 자동화 3대장(Make · n8n · Zapier) 실무 파이프라인 가이드를 바로 이어서 읽어보시고, 내일부터 시작되는 정책금융 계좌 환승 일정은 2026 청년미래적금 계좌개설·갈아타기 & 청년주택드림청약통장 공식에서 놓치지 말고 점검하세요.
- [당일 연관 필독 · AI & 워크해커] [AI & 워크해커] 코딩 모르는 직장인을 위한 클로드 MCP(커넥터) × 노코드 자동화 3대장(Make · n8n · Zapier) 완벽 비교
- [당일 연관 필독 · 머니 스푼] [머니 스푼] 2026 청년미래적금(연 최대 19.4% 효과·11/16 계좌개설) vs 청년도약계좌 갈아타기 & 청년주택드림청약통장 총정리
- [엑셀 실무 팁 시리즈] LET · LAMBDA · SCAN 함수 3대장으로 지옥의 중첩 IF 수식을 나만의 한글 함수와 자동 누적 잔액표로 압축하는 법
- [엑셀 실무 팁 시리즈] TEXTSPLIT · TEXTBEFORE · TEXTAFTER 함수로 텍스트 나누기 마법사 없이 주소·상품코드 1초 자동 분리하는 법
- [엑셀 실무 팁 시리즈] FILTER · UNIQUE · SORT 함수 3대장 조합으로 매크로 없이 실시간 자동 갱신 보고서 만드는 법
