들어가기 전에
Excel에는 많은 함수들이 갖추어져 있어서 거의 모든 작업에 사용할 수 있습니다. 예를 들어, 데이터 검색에는 XMATCH , 논리 연산에는 IF, 조건부 합계에는 SUMIF 함수를 사용할 수 있습니다. 하지만 텍스트에서 특정 패턴을 추출하거나, 복잡한 방식으로 문자열을 조작해야 한다면 어려움을 겪을 수 있으며, 이럴 때 REGEX를 사용하면 도움이 됩니다.
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: Excel’s hidden REGEX and SCAN functions do way more than you expec
- URL: https://www.makeuseof.com/excel-regex-and-scan-functions-do-way-more/
REGEX 함수 이해
REGEX(Regular Expressions)는 특정 규칙에 따라 텍스트를 찾거나, 추출하거나, 바꾸는 데 사용되는 패턴 매칭 언어입니다. "apple"처럼 정확한 텍스트를 검색하는 대신, "a로 시작하고 e로 끝나는 모든 단어" 또는 "셀에 있는 모든 이메일 주소"와 같은 패턴을 검색할 수 있습니다.
REGEX는 Excel의 FIND 함수를 더욱 스마트하게 구현한 버전으로, 기본적인 조회에 적합합니다. 하지만 REGEX는 더 복잡한 시나리오에도 적합합니다. 예를 들어, 복잡한 텍스트에서 전화번호를 추출하거나, 괄호 안의 모든 내용을 추출하거나, 이메일 형식의 유효성을 검사해야 하는 경우 REGEX를 사용합니다.
다른 프로그래밍 언어나 도구에서 패턴 매칭을 사용해 본 적이 있다면 Excel의 정규식 함수도 비슷하게 작동합니다. 이 함수는 사용자가 정의한 패턴을 텍스트에서 검색한 후, 찾은 패턴에 따라 작업을 수행합니다. 패턴 자체는 특수 문자와 구문을 사용합니다. 예를 들어 숫자는 \d, 대문자는 [AZ] 와 같이 처음에는 이해하기 어려울 수 있지만, 연습하면 직관적으로 이해할 수 있습니다.
Excel에서 사용할 수 있는 3가지 REGEX 함수
REGEXTEST
텍스트에 패턴이 있는지 확인하고 TRUE 또는 FALSE를 반환합니다. 구문은 다음과 같습니다.
=REGEXTEST(text, pattern, [mode])
- text: 확인하려는 셀이나 문자열
- pattern: 검색할 정규식 패턴
- mode(선택 사항): 대소문자 구분을 제어(대소문자를 구분하지 않고 일치시키려면 "i" 사용)
판매 데이터 스프레드시트에 제품 코드 열이 있고, 최소 세 자리 연속 숫자를 포함하는 항목에 플래그를 지정하려는 경우를 가정해 보겠습니다. 다음을 사용할 수 있습니다.
=REGEXTEST(A2, "\d{3}")
A2 셀에 "PRD-12345-X"가 포함되어 있으면 함수는 행에서 세 자리 숫자를 찾았으므로 열 B에서 TRUE를 반환합니다.
REGEXEXTRACT
패턴을 기반으로 문자열에서 특정 텍스트를 가져옵니다.
=REGEXEXTRACT(text, pattern, [mode], [instance])
- text: 원본 텍스트
- pattern: 추출할 내용을 정의하는 REGEX 패턴
- mode(선택 사항): 대소문자 구분 제어("i"는 대소문자를 구분하지 않음)
- instance(선택 사항): 일치 항목이 여러 개 있는 경우 반환할 항목(첫 번째는 1개, 두 번째는 2개 등)
판매 데이터에서 C열에는 다른 텍스트와 혼합된 고객 이메일이 포함되어 있습니다. 이메일만 추출하려면 다음을 사용합니다.
=REGEXEXTRACT(B2, "[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}")
이 패턴은 표준 이메일 형식을 식별하여 D열에서 깔끔하게 추출합니다.
REGEXREPLACE
패턴과 일치하는 텍스트를 다른 텍스트로 바꿉니다.
=REGEXREPLACE(text, pattern, replacement, [mode], [instance])
- 텍스트: 원본 텍스트
- 패턴: 찾을 값
- 교체: 교체할 값
- 모드(선택 사항): 대소문자 구분 여부
- 인스턴스(선택 사항): 어떤 발생 항목을 대체할지 결정(모두 대체하려면 비워 둠)
E열에 다양한 형식의 전화번호가 있는 경우(대시가 있는 것도 있고 괄호가 있는 것도 있음) 다음 공식을 사용하여 숫자를 제외한 모든 내용을 제거하여 표준화할 수 있습니다.
=REGEXREPLACE(C2, "[^0-9]", "")
패턴 [^0-9] 는 "숫자가 아닌 모든 것"을 의미하며, 이를 빈 문자열로 바꾸면 숫자만 남습니다.
이 세 가지 함수는 대부분의 텍스트 조작 요구를 충족합니다. 하지만 SCAN 함수와 함께 사용하면 더욱 유연하게 작업할 수 있으며, 특히 여러 행에 걸친 데이터 처리나 반복되는 패턴 추출에 유용합니다.
SCAN 함수 REGEX 조합
SCAN 함수는 배열을 행 단위로 처리하고 누적 결과를 반환합니다. 단일 셀에서 작동하는 표준 함수와는 달리, 전체 범위를 탐색하며 각 값에 함수를 적용하고 이전 값을 추적합니다.
SCAN 함수 사용 구문은 다음과 같습니다.
=SCAN([initial_value], array, lambda)
- initial_value (선택 사항): 계산 시작점 선택(생략하면 SCAN은 배열의 첫 번째 요소를 초기 누산기 값으로 사용하므로 lamda 함수 연산은 해당 요소부터 시작됨)
- array: 처리할 셀 범위
- lambda: 각 값으로 무엇을 할지 정의하는 사용자 정의 함수(lambda를 사용하면 사람처럼 수식을 작성할 수 있으며, 누적 결과와 현재 값, 두 개의 인수를 받음)
SCAN 함수는 단독으로 합계 계산이나 조건부 처리에 유용합니다. 하지만 REGEX 함수와 함께 사용하면 전체 열에서 패턴을 추출하고 하나의 수식으로 복잡한 데이터를 정리할 수 있습니다.
실제 예를 살펴보겠습니다. 판매 데이터 스프레드시트의 G열에는 "$250 결제 완료" 또는 "$75.50 환불 처리 완료"와 같이 텍스트 안에 금액이 포함된 거래 내역이 있습니다. 금액만 있으면 됩니다. 표준 REGEXEXTRACT 수식은 하나의 셀에 적용됩니다.
=REGEXEXTRACT(D2, "\$\d+\.?\d*")
하지만 전체 열을 처리하려면 일반적으로 수식을 수백 행 아래로 끌어내려야 합니다. SCAN을 사용하면 다음 명령만 사용하여 모든 작업을 한 번에 처리할 수 있습니다.
=SCAN("", G2:G11, LAMBDA(acc, curr, REGEXEXTRACT(curr, "\$[\d,]+\.?\d*")))
이 수식은 G2부터 G11까지 셀을 반복하며 각 셀에서 달러 금액을 추출하고 전체 배열을 반환합니다. 여기서는 단순히 추출만 하므로 누적 값(acc)은 필요하지 않지만, SCAN 함수는 람다 구조에서 여전히 누적 값을 필요로 합니다.
이전 결과를 기반으로 데이터를 확장해야 할 때 유용합니다. 예를 들어, I열에서 형식이 일관되지 않은 주문 ID를 추적한다고 가정해 보겠습니다. 어떤 주문 ID는 접두사가 있고 어떤 주문 ID는 접두사가 없습니다. 숫자 부분을 추출하여 유효한 ID의 누적 개수를 생성하려면 다음 수식을 사용합니다.
=SCAN(0, E2:E100, LAMBDA(count, id, IF(REGEXTEST(id, "\d{4,}"), count+1, count)))
위 수식은 REGEXTEST 함수를 사용하여 각 셀에 최소 네 자리 연속 숫자가 있는지 확인합니다. 연속 숫자가 발견되면 카운터를 증가시키고, 그렇지 않으면 J열에 이전 숫자를 유지합니다. 그 결과, 목록을 아래로 내려갈수록 누적된 유효 ID가 표시되는 열이 생성됩니다.
SCAN은 단일 수식에서 여러 단계의 REGEX 연산을 처리합니다. 텍스트를 한 번에 추출, 검증 및 변환해야 하는 경우, SCAN 함수 안에 REGEX 함수를 중첩하면 도우미 열 없이도 해당 작업을 수행할 수 있습니다. 이는 열을 추가하면 속도가 느려질 수 있는 대용량 데이터세트를 작업할 때 특히 유용합니다.
REGEX와 SCAN은 VLOOKUP이나 IF 함수를 대체하는 것은 아니지만, 정확한 일치보다 텍스트 패턴이 더 중요할 때 유용한 도구입니다. 가져온 데이터를 정리하거나 구조화되지 않은 텍스트에서 특정 부분을 추출하는 경우, 수동 편집이나 중첩된 TEXT 함수에 비해 시간을 절약할 수 있습니다.
마치며
처음에는 구문이 어려워 보일 수 있지만, 이메일 유효성 검사, 전화번호 추출, 이름 분리 등 몇 가지 패턴을 익히면 여러 프로젝트에서 재사용할 수 있습니다. 이상적으로는 REGEXTEST로 패턴을 확인한 후, 실제 텍스트가 필요할 때 REGEXEXTRACT로 이동하는 것이 좋습니다. 전체 열을 처리하고 결과를 실행해야 하는 경우에는 SCAN을 추가하세요.
'Excel' 카테고리의 다른 글
| Excel 표가 제대로 작동하지 않는 이유와 해결 방법 (0) | 2025.12.24 |
|---|---|
| 복잡한 계산을 해결하기 위해 사용하는 5가지 Excel 배열 수식 (0) | 2025.12.23 |
| 더 스마트하게 일하는 10가지 간단한 엑셀 활용법 (0) | 2025.12.22 |
| Excel에서 피벗 테이블 데이터 쉽게 검색하는 방법 (1) | 2025.12.19 |
| 내가 가장 많이 사용하는 Excel 함수(그리고 그 이유) (0) | 2025.12.16 |
