들어가기 전에
엑셀은 숫자 계산에는 뛰어나지만 패턴 찾기는 이것과는 전혀 다른 문제입니다. 예를 들어, 이메일 형식을 검증하거나 복잡한 데이터 세트에서 전화번호를 추출하려면 복잡하고 어려운 수식을 만들어야 했습니다. 프로그래머들이 수십 년 동안 사용해 온 패턴 일치 기능을 스프레드시트에서 바로 사용할 수 있도록 해주는 정규 표현식 함수를 소개합니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: This is the Excel feature I've been waiting 10 years for
- URL: https://www.howtogeek.com/microsoft-excel-unique-function-cant-skip-columns-unless-use-trick/
REGEXTEST: 패턴 쉽게 찾기
정규 표현식(REGEXTEST)은 텍스트 문자열이 특정 패턴과 일치하는지 확인합니다. 이 함수는 TRUE 또는 FALSE만 반환하며, 그 이상의 결과는 제공하지 않습니다. 따라서 데이터 유효성 검사, 필터링 및 조건부 서식 지정 에 이상적입니다.
구문은 간단합니다.
=REGEXTEST(text, pattern, [case_sensitivity])
- text: 패턴과 비교하려는 문자열
- pattern: 일치시킬 정규 표현식 패턴
- case_sensitivity: 선택적 매개변수
- 대소문자를 구분하여 검색하려면 0(기본값)으로 설정
- 대소문자를 구분하지 않으려면 1로 설정
REGEXTEST를 사용하기 전에는 제품 코드가 특정 형식을 따르는지 확인하려면 LEN, ISNUMBER, MID 함수를 연결하여 알아보기 힘든 코드를 작성해야 했습니다. 이제는 하나의 수식으로 간단하게 해결할 수 있습니다.
스프레드시트의 제품 코드 열에는 "PRD-12345-X", "ITEM-987", "A12B34"와 같은 항목이 있습니다. 네 자리 이상의 연속된 숫자가 포함된 코드를 확인하려면 다음 수식을 사용하면 됩니다.
=REGEXTEST(A2, "\d{4}")
"PRD-12345-X"와 같은 코드는 TRUE를 반환하는 반면, "ITEM-987"은 "987"이 세 자리 숫자에 불과하므로 FALSE를 반환합니다. 마찬가지로 "A12B34"는 네 자리 연속된 숫자가 아니므로 FALSE를 반환합니다.
이런 종류의 유효성 검사를 하려면 예전에는 복잡한 중첩 함수나 VBA 스크립트가 필요했습니다. 하지만 REGEXTEST를 사용하면 단 한 줄로 처리할 수 있고, 심지어 6개월이 지난 후에도 그 내용을 읽을 수 있습니다.
REGEXEXTRACT: 특정 데이터 쉽게 추출
정규 표현식(REGEXEXTRACT)은 특정 패턴을 기반으로 문자열에서 일치하는 텍스트를 추출합니다. TRUE 또는 FALSE를 반환하는 대신, 일치하는 실제 내용을 제공하므로, 깔끔한 데이터와 지저분한 항목을 구분해야 할 때 훨씬 유용합니다.
구문은 동일한 구조를 따릅니다.
=REGEXEXTRACT(text, pattern, [return_mode], [case_sensitivity])
- text: 추출하려는 데이터가 포함된 문자열
- pattern: 캡처할 대상을 정의하는 정규 표현식
- return_mode: 선택적 매개변수. 첫 번째 일치 항목만 반환하려면 0(기본값)으로 설정하고, 모든 일치 항목을 배열 형태로 반환하려면 1로 설정
- case_sensitivity: 선택적 매개변수. 대소문자를 구분하려면 0(기본값), 대소문자를 구분하지 않으려면 1을 사용
스프레드시트의 고객 이메일 열은 이 기능이 왜 중요한지 보여주는 완벽한 예입니다. "연락처: jane.doe@email.com"이나 "정렬 기준: john_smith@domain.org"와 같은 항목은 실제 이메일 주소를 주변 텍스트에 묻어버립니다. 이를 수동으로 추출하는 것은 매우 번거로운 작업입니다.
다음 수식은 이메일 주소만 추출합니다.
=REGEXEXTRACT(B2, "[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}")
첫 번째 항목의 경우 "jane.doe@email.com"이 반환됩니다. 추가 텍스트나 수동 정리 작업이 필요하지 않습니다.
거래 설명 열은 또 다른 실용적인 활용법을 보여줍니다. "250달러 수령"이나 "1,200달러 송장 지불"과 같은 항목에는 금액이 문장 안에 포함되어 있습니다. 다음 수식을 사용하면 "250달러"와 "1,200달러"를 별도의 열로 직접 추출할 수 있습니다.
=REGEXEXTRACT(E2, "\$[\d,]+\.?\d*")
예전에는 FIND, MID, LEN 함수를 조합해서 이 작업을 했는데, 이런 방식은 투박하고 원본 파일 형식이 조금만 바뀌어도 제대로 작동하지 않았습니다. 하지만 REGEXEXTRACT는 패턴이 다양한 구조에 맞춰 자동으로 조정되기 때문에 변형된 형식을 매끄럽게 처리합니다.
REGEXREPLACE: 정밀 편집 속도 향상
REGEXREPLACE 함수는 특정 패턴과 일치하는 텍스트를 찾아 다른 텍스트로 바꿉니다. 이 함수는 전화번호 형식의 불일치, 띄어쓰기 오류, 원치 않는 문자가 열 전체에 흩어져 있는 경우와 같이 데이터 표준화가 필요할 때 사용합니다.
이 구문은 매개변수를 하나 더 추가합니다.
=REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity])
- text: 수정하려는 문자열
- pattern: 대체할 대상을 지정하는 정규 표현식
- replacement: 각 일치 항목을 대체할 텍스트. 캡처 그룹($1, $2)을 사용하여 원본의 일부를 보존할 수 있음
- occurrence: 어떤 일치 항목을 대체할지 지정(선택 사항). 0(기본값)은 모든 일치 항목을 대체하고, 1은 첫 번째 일치 항목을 대체하고, 2는 두 번째 일치 항목을 대체하는 식
- case_sensitivity: 대소문자를 구분하려면 0(기본값)으로 설정하고, 대소문자를 구분하지 않으려면 1로 설정
스프레드시트의 전화번호 열은 이 기능이 왜 유용한지 정확히 보여줍니다. 입력값은 "(123) 456-7890", "123-456-7890", "+1 123 456 7890", "123.456.7890" 등 매우 다양한 형식으로 나타납니다. 이처럼 일관성이 없는 데이터를 대상으로 분석이나 조회를 수행하면 오류가 발생할 가능성이 높습니다.
다음 수식은 숫자만 남기고 나머지를 모두 제거하여 모든 변형을 "1234567890"과 같은 깔끔한 형식으로 변환합니다.
=REGEXREPLACE(D2, "[^\d]", "")
형식화된 출력을 선호하는 경우 다음 수식을 사용하여 모든 항목을 "(123) 456-7890"으로 표준화합니다.
=REGEXREPLACE(REGEXREPLACE(D2, "[^\d]", ""), "(\d{3})(\d{3})(\d{4})", "($1) $2-$3")
SUBSTITUTE 함수는 모든 구분 기호를 미리 알고 있다면 처리할 수 있지만, 괄호, 하이픈, 마침표, 공백을 각각 따로 중첩해서 사용해야 합니다. 반면 REGEXREPLACE 함수는 하나의 패턴으로 이 모든 구분 기호를 처리합니다.
엑셀 함수와 코딩 사이의 간극 극복
수년 동안 정규 표현식은 엑셀 사용자들에게는 들어본 적은 있지만 파이썬, VBA 또는 파워 쿼리로 전환하지 않고는 사용할 수 없는 개념이었습니다. 패턴 일치를 원하면 스크립팅 언어를 배우거나 제대로 작동하지 않는 복잡한 수식을 만들어야 했습니다. 하지만 이제 그 장벽은 사라졌습니다.
이 세 가지 함수는 핵심 프로그래밍 개념을 스프레드시트 환경으로 직접 가져옵니다. 구문은 JavaScript, Python 및 기타 언어 개발자가 사용하는 것과 유사하므로 온라인에서 찾은 정규 표현식 패턴은 거의 수정 없이 Excel에서 사용할 수 있습니다. 이전에는 코드 변환이 필요했던 Stack Overflow 답변도 이제 직접 적용할 수 있습니다.
이는 일반적인 스프레드시트 사용자와 전문 개발자 사이의 중간 단계에 있는 사람들에게 가장 중요합니다. 보고서 자동화는 할 수 있지만, 모든 데이터 정리 작업을 위해 파이썬 스크립트를 작성할 만큼은 모르는 경우처럼 말이죠. 정규 표현식(REGEX) 함수는 바로 이러한 간극을 완벽하게 메워줍니다.
마치며
학습 곡선 또한 완만합니다. \d+숫자나 [A-Za-z]+문자 같은 기본 패턴으로 대부분의 일반적인 작업을 처리할 수 있습니다. 모든 정규 표현식 기능을 마스터할 필요는 없고, 당면한 문제를 해결하는 데 필요한 만큼만 알면 됩니다. 복잡하고 여러 단계를 거치는 작업에는 여전히 Power Query를 사용하여 데이터를 변환하는 방법이 유용하지만, 간단한 패턴 일치를 위해 별도의 편집기를 여는 것은 항상 번거로웠습니다. 이제 워크시트에서 단 하나의 수식으로 동일한 결과를 얻을 수 있습니다.
'Excel' 카테고리의 다른 글
| Excel의 LAMBDA, SCAN 함수의 차이점은 무엇이며, 함께 사용하는 법 (0) | 2026.05.11 |
|---|---|
| 긴 Excel 수식보다 보조 열이 더 나은 이유 (1) | 2026.05.08 |
| 지루한 작업을 몇 시간씩 줄여줄 구글 시트 함수 5가지 (0) | 2026.05.06 |
| 엑셀 파일이 엄청나게 커지는 이유와 파일 크기를 획기적으로 줄이는 5가지 방법 (0) | 2026.04.30 |
| 10배 더 빠르게 이동할 수 있는 숨겨진 Excel 우클릭 활용법 (0) | 2026.04.29 |
