들어가기 전에
정규 표현식(REGEX: Regular expressions)은 지정한 패턴과 일치하는 텍스트 문자열을 추출하거나 대체하는 데 사용할 수 있는 검색 패턴을 말합니다. REGEX 함수는 Windows나 Mac에서 Microsoft 365용 Excel과 웹용 Excel 사용자를 대상으로 제공됩니다. Excel에서 REGEX 함수를 사용하는 방법에 대한 간소화된 요약과 예제를 소개합니다.
권현욱(엑셀러) | 아이엑셀러 닷컴 대표 · Microsoft MVP · 엑셀 솔루션 프로바이더 · 작가
이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: How to Use the REGEX Functions in Excel
- URL: https://www.howtogeek.com/how-to-use-the-regex-functions-in-excel/
REGEXTEST 사용 방법
이 함수는 텍스트 문자열이 주어진 패턴과 일치하는지 테스트하여 이 테스트에 따라 참 또는 거짓을 반환합니다. 데이터가 특정 패턴을 따르는지 테스트하는 좋은 방법이며, 사용 구문은 다음과 같습니다.
REGEXTEST(a,b,c)
- a(필수): 테스트할 텍스트, 값 또는 테스트하려는 텍스트가 포함된 셀 참조
- b(필수): 테스트를 수행하는 데 사용되는 패턴
- c(선택 사항): 테스트에서 대소문자를 구분하려면 0, 구분하지 않으려면 1
REGEXTEST 사용 방법 예시
이 스프레드시트에는 엄격한 구조를 따라야 하는 제품 코드 목록이 포함되어 있습니다.
Valid code에는 다음과 같은 사항이 포함됩니다.
- 제품 크기를 소문자로 표시(초소형은 “xs”, 스몰은 “s”, 미디엄은 “m” 등)
- 제품의 재질을 나타내는 1~2자리 숫자
- 제품 제조지를 나타내는 세 개의 대문자
- 위의 각 부분 사이에 대시
모든 제품 코드가 이 구조와 일치하는지 테스트하고 싶습니다. B2 셀에 다음과 같이 입력합니다.
=REGEXTEST([@Code],“[xs|s|m|l|xl]-[0-9]{1,2}-[A-Z]{3}”,0)
- [@Code]: 테스트하려는 코드가 있는 열에 대한 구조화된 참조
- [xs|s|m|l|xl]: 테스트하려는 제품 코드의 첫 번째 부분이며, 세로줄은 “또는”을 의미
- [0-9]{1,2}: 테스트하려는 제품 코드의 두 번째 부분으로, [0-9]는 한 자리 숫자이고 {1,2}는 한 자리 숫자 하나 또는 두 자리 숫자가 있을 수 있음을 의미
- [A-Z]{3}: 테스트하려는 제품 코드의 세 번째 부분으로, [A-Z]는 임의의 대문자이고 {3}은 정확히 세 개가 있어야 함을 의미
- 테스트하려는 코드의 세 부분은 대시로 구분
- 0은 테스트가 대소문자를 구분한다는 것을 Excel에 알려주는 수식의 마지막 인수
Enter 키를 눌러 이 수식을 B 열의 모든 행에 적용하면 그 결과 코드 중 두 개만 유효(TRUE)한 것으로 표시됩니다.
- m-2-UK: 국가 코드에 대문자 두 자리만 포함되어 있으므로 유효하지 않음(FALSE로 표시됨)
- xl-714-AUS: 재료 코드에 세 자리 숫자가 포함되어 있으므로 유효하지 않음
- S-5-USA: 사이즈 코드가 대문자이므로 유효하지 않음
이 예시에는 [ ] 및 { }와 같은 문자가 포함되어 있습니다. 그러나 테스트를 수행하는 데 사용되는 패턴을 결정하는 데 사용할 수 있는 더 많은 문자(토큰이라고도 함)가 있으며, 그 중 일부는 아래 예제에서 사용하겠습니다.
REGEXEXTRACT로 특정 텍스트 조각 찾기
이 함수는 지정된 패턴에 따라 셀에 있는 텍스트의 일부를 반환합니다. 예를 들어 숫자와 텍스트를 구분하고 싶을 수 있습니다.
REGEXEXTRACT(d,e,f,g)
- d(필수): 추출하려는 텍스트가 포함된 텍스트, 값 또는 셀 참조
- e(필수): 추출하려는 패턴
- f(선택 사항): 첫 번째 일치만 추출하려면 0, 적용 가능한 모든 일치 항목을 배열로 추출하려면 1, 첫 번째 일치 항목에서 그룹을 추출하려면 2
- g(선택 사항): 대소문자를 구분하여 추출하려면 0, 그렇지 않으면 1
형식이 지정된 Excel 테이블은 유출된 배열을 처리할 수 없으므로 인수 f에서 일치 항목을 배열로 추출하려는 경우 데이터의 형식이 일반 형식인지 확인하세요.
REGEXTRACT 사용 방법 예시
이 예에서는 고객의 이름, 성, 전화번호를 3개의 개별 열로 추출하고자 합니다.
먼저 이름에 집중해 보겠습니다. B2 셀에 다음을 입력합니다.
=REGEXEXTRACT(A2,“[A-Z][a-z]+”,1)
- A2: 추출하려는 데이터가 들어 있는 셀
- [A-Z][a-z]+: 대문자 다음에 소문자로 시작하는 모든 단어를 추출하고 싶다는 것을 Excel에 알려주며 “+”는 각 패턴에서 하나 이상의 소문자를 반환하고 싶다는 것을 나타냄
- 1: 위 패턴의 각 예시를 개별 셀로 배열로 분리(즉, B2 셀의 첫 번째 이름과 C2 셀의 두 번째 이름)하려는 것을 나타냄. 이 인수를 생략하면 Excel은 B2 셀에서 첫 번째 일치 항목(첫 번째 이름)만 반환
Enter 키를 누르면 Excel에서 추출이 성공적으로 수행되고 C2 셀 주위에 희미한 파란색 선이 추가되어 유출된 배열임을 알려줍니다.
셀 B2를 선택한 상태에서 셀 오른쪽 하단에 있는 채우기 핸들을 사용하여 이 상대 수식을 나머지 세부 정보 행에 복제할 수 있습니다.
이제 비슷한 REGEXTRACT 수식을 사용하여 고객의 전화 번호를 추출해야 합니다. D2 셀에 다음과 같이 입력합니다.
=REGEXEXTRACT(A2,“[0-9()]+ [0-9-]+”)
- A2: 추출하려는 데이터가 들어 있는 셀
- 0-9()]+: 둥근 괄호 안에 있는 0에서 9까지의 숫자를 추출하고, “+”는 이 패턴에서 하나 이상의 숫자를 추출하며, [0-9-]+는 0에서 9까지의 숫자를 추출
- [0-9-]+는 문자열에서 나머지 숫자를 추출하며, 두 번째 “-”는 전화 번호의 두 부분을 구분하는 대시를 나타내고 “+”는 문자열에 하나 이상의 숫자가 포함되어 있는 경우 추출할 것을 Excel에 알려줌
A 열의 각 셀에 이 패턴의 인스턴스가 하나씩만 있으므로 인수를 더 추가할 필요가 없습니다. 다시 한 번 이 수식이 예상한 결과를 생성하는지 확인했으면 채우기 핸들을 사용하여 D 열의 나머지 셀에 이 수식을 복제할 수 있습니다.
[참고] Excel에서 데이터를 추출하고 비슷한 결과를 얻는 다른 방법으로는 텍스트 분할 함수 또는 Excel의 [빠른 채우기]를 사용하는 것이 있습니다.
REGEXREPLACE로 데이터 조작하기
이 함수는 셀의 텍스트를 가져와 다른 셀에 해당 데이터의 새 버전을 만듭니다. 이 함수의 이름이 REGEXREPLACE이긴 하지만 실제로 원래 위치의 원본 텍스트를 대체하지는 않습니다.
REGEXREPLACE(h,i,j,k,l)
- h(필수): 바꾸려는 텍스트가 포함된 텍스트, 값 또는 셀 참조
- i(필수): 바꾸려는 패턴
- j(필수): 만들려는 대체 항목
- k(선택 사항): 바꾸려는 패턴의 발생 위치
- l(선택 사항): 대소문자를 구분하려면 0, 그렇지 않으면 1
REGEXREPLACE 사용 방법 예시
아래에서 A 열의 이름 목록을 볼 수 있습니다. 목표는 이러한 이름을 B 열에 다시 만들되, 이름을 구분하는 쉼표를 포함하여 “성, 이름” 형식을 사용하는 것입니다.
B2 셀에 다음과 같이 입력합니다.
=REGEXREPLACE([@Client name],“([A-Z][a-z]+) ([A-Z][a-z]+)”,“$2, $1”)
- [@Client name]: 영향을 주고자 하는 데이터가 포함된 열 참조
- 수식에 [A-Z][a-z]+가 두 번 포함되어 있고 공백으로 구분되어 있으면 대문자 다음에 하나 이상의 소문자가 포함된 두 문자열을 가져오고 싶다는 것을 Excel에 알려줌
- 2, $1: 쉼표와 공백으로 구분된 두 텍스트 문자열의 순서를 반대로 하라는 의미. $ 기호를 포함하지 않으면 Excel은 각 셀에 단순히 “2, 1”을 결과로 반환
위 수식에서 인수 k와 l을 다루지 않은 이유는 Excel에서 모든 항목을 대체하고(인수 k의 기본값), 대소문자를 구분하여 대체하기를 원하기 때문입니다(인수 l의 기본값). 서식 있는 표를 사용하고 있기 때문에 Enter 키를 누르면 수식이 B 열의 나머지 셀에 적용됩니다.
마치며
정규식은 Excel에서만 사용하는 것이 아닙니다. 정규식을 사용하여 복사 붙여넣기한 PDF 텍스트 수정, 다운로드한 파일 이름 일괄 변경, 통화 서식 지정, HTML 태그 제거 등 컴퓨터의 다른 작업도 자동화할 수 있습니다.
'Excel' 카테고리의 다른 글
| 엑셀 수식을 최적화하고 속도를 높이기 위한 8가지 팁 (3) | 2025.02.26 |
|---|---|
| 프로처럼 엑셀을 마스터하기 위해 실제 데이터 세트 만드는 법 (8) | 2025.02.25 |
| Excel 집계표를 표준 테이블로 만드는 3가지 방법 (1) | 2025.02.24 |
| 대규모 데이터에서 엑셀 Power Pivot을 사용하는 5가지 방법 (2) | 2025.02.21 |
| 전문가처럼 Excel 수식을 평가하고 디버깅하는 4가지 팁 (4) | 2025.02.20 |
