엑셀에서 REGEX 함수를 사용해서 정규식 이용하는 방법

엑셀은 보통 간단하게 문서를 만드는것 보다는 데이터를 처리 해서 문서를 만들때 더 많이 사용을 하게 됩니다. 이럴때 알아 두시면 좋은 함수가 바로 REGEX 함수 인데요. REGEX  함수는 정규표현식(Regular Expression) 을 사용해서 엑셀에 있는 텍스트를 원하는 형태로 가공을 할수 있도록 도와 줍니다. 이번 글에서는 이 REGEX  함수를 사용하는 방법에 대해서 알아 보도록 하겠습니다. 

정규표현식이란?

정규표현식을 들어보신 분들도 계시겠지만 생소하신 분들도 있을거 같습니다. 정규식은 특정한 패턴이나 규칙을 기반으로 텍스트를 검색을 하고, 추출을 하거나, 수정을 하는 방식을 이양기 합니다. 보통 프로그래밍을 해보신 분들은 많이 아실거라고 생각이 됩니다. 

엑셀에서도 데이터를 가공하는 방법이 많이 있지만 마찬가지로 정규식을 사용할수 있는 REGEX 함수를 제공을 하고 있죠. 이번 포스팅에서는 여러가지 예를 통해서 이 REGEX 함수를 사용하는 방법에 대해서 설명 드리도록 하겠습니다. 

엑셀에서 제공하는 REGEX 함수들은?

엑셀에서는 REGEX 함수 하나가 아니라 관련 된 여러개의 함수를 사용을 할수가 있습니다. 아래는 엑셀에서 사용할수 있는 REGEX 함수들 입니다. 

REGEXTEST: 텍스트 패턴 일치 여부 확인

용도: 특정 텍스트가 주어진 패턴과 일치하는지 확인. 결과는 TRUE 또는 FALSE로 반환됩니다.

문법 : =REGEXTEST(a, b, c)

• a: 테스트할 텍스트가 포함된 셀 참조.
• b: 비교할 패턴.
• c: 선택적으로 대소문자를 구분(0: 구분, 1: 구분 안 함).

예제 : =REGEXTEST(A1, "[xs|s|m|l|xl]-[0-9]{1,2}-[A-Z]{3}", 0)

• 설명:
  • [xs|s|m|l|xl]: 제품 사이즈 패턴.
  • [0-9]{1,2}: 1~2자리 숫자.
  • [A-Z]{3}: 3자리 대문자.
  • -는 세 부분을 구분하는 하이픈.
• 결과: 셀의 텍스트가 이 구조를 따르면 TRUE, 아니면 FALSE.

---

REGEXEXTRACT: 특정 텍스트 추출

용도: 텍스트에서 지정된 패턴에 맞는 부분만 추출.

문법 : =REGEXEXTRACT(d, e, f, g)

• d: 추출 대상 텍스트가 포함된 셀 참조.
• e: 추출할 패턴.
• f: 첫 번째 일치(0), 모든 일치 배열(1), 그룹(2) 선택.
• g: 선택적으로 대소문자 구분(0: 구분, 1: 구분 안 함).

예제 : =REGEXEXTRACT(A2, "[A-Z][a-z]+", 1)

• 설명:
  • [A-Z][a-z]+: 대문자로 시작하고 소문자가 뒤따르는 단어.
  • 1: 배열로 각 일치를 분리해 반환.
• 결과: 영문 이름과 성을 각각 다른 셀에 추출.

---

REGEXREPLACE: 텍스트 대체

용도: 특정 패턴에 맞는 텍스트를 다른 텍스트로 대체.

문법 : =REGEXREPLACE(h, i, j, k, l)

• h: 대체 대상 텍스트가 포함된 셀 참조.
• i: 대체할 패턴.
• j: 새 텍스트.
• k: 대체할 패턴의 발생 횟수.
• l: 선택적으로 대소문자 구분(0: 구분, 1: 구분 안 함).

예제 : =REGEXREPLACE(A1, "([A-Z][a-z]+) ([A-Z][a-z]+)", "$2, $1")

• 설명:
  • 영문 이름과 성의 순서를 변경해 "성, 이름" 형식으로 변환.
  • $2, $1: 두 번째 그룹이 먼저 나오도록 순서를 바꿈.

엑셀 REGEX 함수 예제

1. 예제 1: 이메일 주소 유효성 검증

test@example.com

invalid-email

user@domain.co.kr

user@.com

example@domain

1.2 검증할 규칙

• 이메일 주소는 다음 조건을 충족해야 유효하다고 간주합니다:
  • @ 앞에 영문자, 숫자, 또는 특수문자(. 또는 -) 포함 가능.
  • @ 뒤에 도메인이 존재하고, .com, .net, .org와 같은 최상위 도메인이 있어야 함.

1.3 REGEX 공식

셀 B2에 다음 공식을 입력합니다

=REGEXTEST(A2, "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$", 0)

1.4 설명

• 패턴:
  • ^[A-Za-z0-9._%+-]+: @ 앞 부분에 알파벳, 숫자, 또는 특수문자(._%+-) 허용.
  • @[A-Za-z0-9.-]+: @ 뒤 도메인 부분에 알파벳, 숫자, 또는 .와 - 허용.
  • \.[A-Za-z]{2,}$: 도메인 끝부분은 최소 2자리 이상의 알파벳.
• 결과:
  • TRUE: 유효한 이메일.
  • FALSE: 유효하지 않은 이메일.

1.5 결과 예시

---

2. 예제 2: 한글 텍스트 처리 (특정 단어 추출)

2.1 시트 데이터

나는 오늘 서울에 갔다.

부산에서 출발한 기차가 도착했다.

제주도 여행을 계획 중이다.

강릉 바닷가가 정말 아름답다.

대전은 역사적인 도시다.

2.2 목표

• 문장에서 지역 이름(서울, 부산, 제주도, 강릉, 대전)을 추출.

2.3 REGEX 공식

셀 B2에 다음 공식을 입력합니다:

=REGEXEXTRACT(A2, "서울|부산|제주도|강릉|대전")

2.4 설명

• 패턴:
  • 서울|부산|제주도|강릉|대전: 지역 이름을 선택적으로 찾는 OR 조건.
• 결과:
  • 문장에서 지역 이름이 발견되면 해당 지역 이름 반환.
  • 문장에 해당 패턴이 없으면 #VALUE! 오류 반환.

2.5 결과 예시

---

3. 활용 팁 및 확장

3.1 REGEXREPLACE로 한글 수정

특정 단어를 다른 단어로 교체할 경우:

excel

코드 복사

=REGEXREPLACE(A2, "서울", "Seoul")

• 문장에서 "서울"을 "Seoul"로 교체.

3.2 배열 결과 처리

한 문장에서 여러 단어를 추출해 배열로 출력하려면:

excel

코드 복사

=REGEXEXTRACT(A2, "서울|부산|제주도|강릉|대전", 1)

• 모든 일치 항목을 배열로 반환(Excel 365 이상).

이번 포스팅에서는 엑셀에서 데이터를 추출 하거나 변경등을 할때 알아 두시면 굉장히 유용한 엑셀 정규식 함수인 REGEX 함수에 대해서 알아 보았습니다. 사용하기에는 정말 좋지만 오래된 버전에서는 제공이 안될수 있다는 점은 참고 하시ㅣ기 바랍니다. 만약에 함수에서 에러가 난다면 제공을 하지 않고 있는 버전이니 다른 방법을 찾으셔야 합니다.