---
title: "[엑셀 실무 팁] REGEXTEST · REGEXEXTRACT · REGEXREPLACE 정규식 함수 3대장과 범위 축소 연산자(.:.)로 비정형 텍스트 추출·개인정보 마스킹 1초 자동화하는 법"
url: https://soongin.com/2026/11/15/excel-regextest-regexextract-regexreplace-trimrange-auto-parsing-tip/
author: 김형백 (soongin.com)
date_published: 2026-11-15T03:12:44+09:00
date_modified: 2026-11-15T03:28:26+09:00
categories: "MS 엑셀 강좌, 엑셀 Tips"
---

# [엑셀 실무 팁] REGEXTEST · REGEXEXTRACT · REGEXREPLACE 정규식 함수 3대장과 범위 축소 연산자(.:.)로 비정형 텍스트 추출·개인정보 마스킹 1초 자동화하는 법

[30초 핵심 요약 브리핑] 엑셀 정규표현식(REGEX) 3대 함수 & 점(.:.) 범위 축소 연산자 실무 공식

    - **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초 자동 분리하는 법](https://soongin.com/2026/11/11/excel-textsplit-textbefore-textafter-auto-text-parsing-tip/)만으로도 훌륭하게 해결됩니다. 하지만 구분 기호 자체가 없거나 전화번호에 하이픈(`-`)을 넣은 사람과 안 넣은 사람이 섞여 있을 때는 결국 **'정규표현식(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대장 실시간 자동 갱신 보고서 만드는 법](https://soongin.com/2026/11/10/excel-filter-unique-sort-dynamic-array-auto-report-tip/)에서는 단순 일치 조건(`=`)만 걸 수 있었지만, 이제 `FILTER`의 두 번째 인수에 `REGEXTEST`를 넣으면 SQL 데이터베이스의 `WHERE ... REGEXP` 쿼리처럼 다중 키워드 부분 검색이 가능해집니다!

### 3-1. '환불' 또는 '파손' 또는 '오배송'이 포함된 CS 행만 실시간 자동 추출하기

기존 엑셀에서 3개 단어가 하나라도 포함된 행을 필터링하려면 `ISNUMBER(SEARCH(...))+ISNUMBER(SEARCH(...))`처럼 끔찍하게 긴 수식을 써야 했습니다. 정규식의 파이프(`|`, OR 연산자)를 쓰면 단 한 줄로 끝납니다. 게다가 어제 배운 [LET · LAMBDA · SCAN 함수로 나만의 한글 함수 압축하는 법](https://soongin.com/2026/11/14/excel-let-lambda-scan-custom-formula-running-total-auto-tip/)과 최신 **트림 참조 연산자(`:.` / `TRIMRANGE`)**를 함께 결합하면 원본 행이 매일 늘어나도 수식을 고칠 필요가 전혀 없습니다.

[실전 마스터 수식: 점(.:.) 자동 범위 축소 + REGEXTEST 다중 키워드 실시간 필터링]

=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) 및 데이터 정제 체크리스트

  Q1. 회사 PC 엑셀에서 `=REGEXEXTRACT`를 쳤더니 `#NAME?` 오류가 뜹니다. 왜 그런가요?
  `REGEXTEST`, `REGEXEXTRACT`, `REGEXREPLACE` 및 `TRIMRANGE` 함수는 **Microsoft 365 구독형 엑셀(Windows·Mac 최신 채널) 및 웹용 엑셀(Excel for the web)** 전용 최신 함수입니다. 사내 PC가 엑셀 2016, 2019, 2021 같은 구형 영구 설치 버전이라면 무료 마이크로소프트 계정으로 브라우저에서 **웹용 엑셀(excel.new)**을 열어 대량 정제 후 값 붙여넣기로 내려받는 우회 팁을 추천합니다.

  Q2. `d`와 `[0-9]`는 둘 다 숫자를 찾는다는데 실무에서 무슨 차이가 있나요?
  매우 중요한 디테일입니다! 엑셀 PCRE2 엔진에서 **`d`는 유니코드 숫자 전반을 매칭하므로 전각 숫자(`１２３４５` 등 특수문자형 숫자)까지 숫자로 인식**해 통과시킬 수 있습니다. 반면 ERP 업로드용 계좌번호나 사업자번호처럼 순수 반각 아라비아 숫자(`0~9`)만 엄격히 걸러내야 할 때는 반드시 **`[0-9]`**를 사용해야 전산 업로드 오류를 막을 수 있습니다.

  Q3. `REGEXEXTRACT`로 추출한 숫자로 `SUM` 합계를 구했더니 0이 나옵니다!
  `REGEXEXTRACT`와 `REGEXREPLACE`의 반환값은 항상 **'텍스트(문자열)'** 자료형입니다. 따라서 숫자만 뽑아낸 뒤 합계나 크기 비교에 쓰려면 수식 맨 앞에 이중 마이너스 부호(**`=--REGEXEXTRACT(A2, "[0-9]+")`**)를 붙이거나 `VALUE()` 함수로 감싸 진짜 수치형 데이터로 변환해 주어야 정상 합산됩니다.

  Q4. 마침표(`.`)나 괄호(`()`), 달러(`$`) 문자 그 자체를 찾고 싶을 때는 어떻게 하나요?
  정규식에서 `. + * ? ^ $ ( ) [ ] { } |` 기호는 특별한 명령어로 예약된 메타 문자입니다. 만약 이메일 도메인의 실제 마침표나 금액의 괄호 기호 자체를 글자로 찾고 싶다면 반드시 앞에 역슬래시(원화 기호 ``)를 붙여 **`.`, `(`, `)`**처럼 이스케이프(Escape) 처리해 주어야 오작동하지 않습니다.

  [실행 체크리스트] 야근 없애는 엑셀 정규식 & 트림 참조 적용 5단계

    - [ ] 외부 공유용 고객·인사 시트 배포 전 `REGEXREPLACE`로 주민등록번호 뒷자리(`$1******`)와 연락처 가운데 자리(`****`) 비식별화 마스킹 적용하기

    - [ ] 숫자 검증 시 전각 문자 혼입 방지를 위해 `d` 대신 엄격한 반각 패턴인 **`[0-9]`** 사용하기

    - [ ] 추출된 금액·수량 문자열을 계산에 쓸 때는 수식 앞에 **`--`(이중 음수 연산자)**를 붙여 숫자 형식으로 즉시 변환하기

    - [ ] 추출 대상이 없는 빈 메모 행에서 `#N/A`가 뜨지 않도록 **`IFNA(수식, "")`**으로 깔끔하게 예외 처리하기

    - [ ] 동적 배열 수식에 전체 열(`A:D`) 대신 **`A2:.D50000` 트림 참조 연산자**를 적용해 시트 재계산 속도 극대화하기

엑셀에서 정제·마스킹을 마친 데이터를 노션·슬랙·구글 시트로 24시간 자동 전송하는 다음 단계가 궁금하시다면 오늘 함께 발행된 [클로드 MCP(커넥터) × 노코드 자동화 3대장(Make · n8n · Zapier) 실무 파이프라인 가이드](https://soongin.com/2026/11/15/claude-mcp-connectors-make-n8n-zapier-nocode-office-automation-guide/)를 바로 이어서 읽어보시고, 내일부터 시작되는 정책금융 계좌 환승 일정은 [2026 청년미래적금 계좌개설·갈아타기 & 청년주택드림청약통장 공식](https://soongin.com/2026/11/15/2026-youth-future-savings-vs-leap-account-switch-housing-dream-guide/)에서 놓치지 말고 점검하세요.

  [함께 읽으면 좋은 실전 가이드] 스마트 워크 & 머니 스푼 추천 칼럼

    - **[당일 연관 필독 · AI & 워크해커]** [[AI & 워크해커] 코딩 모르는 직장인을 위한 클로드 MCP(커넥터) × 노코드 자동화 3대장(Make · n8n · Zapier) 완벽 비교](https://soongin.com/2026/11/15/claude-mcp-connectors-make-n8n-zapier-nocode-office-automation-guide/)

    - **[당일 연관 필독 · 머니 스푼]** [[머니 스푼] 2026 청년미래적금(연 최대 19.4% 효과·11/16 계좌개설) vs 청년도약계좌 갈아타기 & 청년주택드림청약통장 총정리](https://soongin.com/2026/11/15/2026-youth-future-savings-vs-leap-account-switch-housing-dream-guide/)

    - **[엑셀 실무 팁 시리즈]** [LET · LAMBDA · SCAN 함수 3대장으로 지옥의 중첩 IF 수식을 나만의 한글 함수와 자동 누적 잔액표로 압축하는 법](https://soongin.com/2026/11/14/excel-let-lambda-scan-custom-formula-running-total-auto-tip/)

    - **[엑셀 실무 팁 시리즈]** [TEXTSPLIT · TEXTBEFORE · TEXTAFTER 함수로 텍스트 나누기 마법사 없이 주소·상품코드 1초 자동 분리하는 법](https://soongin.com/2026/11/11/excel-textsplit-textbefore-textafter-auto-text-parsing-tip/)

    - **[엑셀 실무 팁 시리즈]** [FILTER · UNIQUE · SORT 함수 3대장 조합으로 매크로 없이 실시간 자동 갱신 보고서 만드는 법](https://soongin.com/2026/11/10/excel-filter-unique-sort-dynamic-array-auto-report-tip/)