Excel에서 REGEX 함수를 사용하는 방법

Excel에서 REGEX 함수를 사용하는 방법

정규 표현식(REGEX)은 텍스트 문자열이 주어진 패턴에 부합하는지 확인하고, 

주어진 패턴과 일치하는 문자열을 추출하거나 대체하는 데 사용할 수 있는 검색 패턴입니다. 

 

복잡성을 고려할 때, 이 글에서는 엑셀에서의 사용 예시와 요약을 간소화하여 제공합니다.

REGEX 기능은 Windows 또는 Mac에서 Microsoft 365용 Excel을 사용하는 사람들뿐만 아니라

 웹에서도 Excel을 사용하는 사람들에게도 사용할 수 있습니다.

REGEXTEST 사용 방법

이 함수는 텍스트 문자열이 주어진 패턴과 일치하는지 여부를 테스트하며, 

이 테스트를 기반으로 TRUE 또는 FALSE를 반환합니다. 

 

이것은 데이터가 특정 패턴을 따르는지 테스트하는 좋은 방법입니다.

구문

REGEXTEST(a,b,c)

 

 

a(required)는 테스트하려는 텍스트를 포함하는 텍스트, 값 또는 셀 참조입니다.

b (required)는 테스트를 수행하는 데 사용되는 패턴입니다.

c(선택 사항)는 대소문자 구분 테스트를 원하는 경우 0이거나, 그렇지 않은 경우 1입니다.

 

 

REGEXTEST 사용 방법 예시

이 스프레드시트에는 엄격한 구조를 따라야 하는 제품 코드 목록이 포함되어 있습니다.

유효한 코드에는 다음이 포함됩니다:

제품 크기의 소문자 표현("초소형의 경우 xs", 소형의 경우 "s", 중형의 경우 "m" 등)
제품의 재질을 나타내는 한 자리 또는 두 자리 숫자,
제품이 제조되는 위치를 나타내는 대문자 세 개와
위에서 설명한 세 부분 각각 사이의 대시.

모든 제품 코드가 이 구조와 일치하는지 테스트하고 싶습니다.

그래서 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은 엑셀에게 테스트가 대소문자 구분임을 알려주는 공식의 마지막 인수입니다.

열 B의 모든 행에 이 공식을 적용하려면 Enter 키를 누르면 

결과적으로 두 개의 코드만 유효하다는 것이 나타납니다(TRUE).

 

m-2-UK는 국가 코드에 대문자 두 개만 포함되어 있기 때문에 유효하지 않습니다(FALSE 결과로 표시됨).

재료 코드에 세 자리 숫자가 포함되어 있으므로 xl-714-AUS는 유효하지 않습니다.

S-5-USA는 크기 코드가 대문자이므로 유효하지 않습니다.

이 예제에는 [ ] 및 { }와 같은 문자가 포함되어 있습니다. 

그러나 테스트를 수행하는 데 사용되는 패턴을 결정하는 데 사용할 수 있는 

더 많은 문자(토큰이라고도 함)가 있으며, 그 중 일부는 아래 예시에서 사용할 것입니다.

REGEXTRACT: 특정 텍스트 찾기

이 함수는 지정된 패턴에 따라 셀의 텍스트 일부를 반환합니다. 

예를 들어, 숫자와 텍스트를 구분하는 것이 좋습니다.

REGEXEXTRACT(d,e,f,g)

 

d (required)는 추출하려는 텍스트를 포함하는 텍스트, 값 또는 셀 참조입니다.

e(required)는 추출하려는 패턴입니다.

f(선택 사항)는 첫 번째 매치만 추출하려면 0이고, 1은 해당되는 모든 매치를 배열로 추출하고,

2는 첫 번째 매치에서 그룹을 추출하는 것입니다.

g(선택 사항)는 대소문자 구분을 원하는 경우 0이거나, 그렇지 않은 경우 1입니다.

형식화된 Excel 테이블은 유출된 배열을 처리할 수 없으므로, 

인수 f에서 배열로 일치 항목을 추출하려면 데이터가 명확하게 형식화되어 있는지 확인해야 합니다.

 

REGEXTRACT 사용 방법 예시

이 예제에서는 고객의 이름, 성, 전화번호를 세 개의 별도 열로 추출하고자 합니다.

 

먼저 이름에 집중해 보겠습니다. 

셀 B2에서는 다음과 같이 입력합니다:

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

A2는 제가 추출하고자 하는 데이터를 포함하는 셀입니다.

[A-Z][a-z]+는 엑셀에게 대문자와 소문자로 시작하는 단어를 추출하고 싶다고 말합니다. 

"+"는 각 패턴에서 하나 이상의 소문자를 반환하고 싶다는 것을 나타내며,

1은 위 패턴의 각 예를 개별 셀(즉, 셀 B2의 첫 번째 이름과 셀 C2의 두 번째 이름)로 

배열하여 분리하기를 원한다는 것을 나타냅니다. 

 

이 인수를 생략하면 엑셀은 셀 B2에서만 첫 번째 일치(이름)를 반환합니다.

Enter 키를 누르면 Excel이 성공적으로 추출을 수행하고 

셀 C2 주변에 희미한 파란색 선을 추가하여 유출된 배열임을 알립니다.

 

셀 B2를 선택하면 이제 셀의 오른쪽 하단 모서리에 있는 채우기 핸들을 사용하여 

이 상대 공식을 나머지 세부 사항 행과 복제할 수 있습니다.

이제 유사한 REGEXTRACT 공식을 사용하여 고객의 전화번호를 추출해야 합니다. 

셀 D2에서는 다음과 같이 입력합니다:

=REGEXEXTRACT(A2,"[0-9()]+ [0-9-]+")

 

A2는 제가 추출하고자 하는 데이터를 포함하는 셀입니다.

[0-9 ()]+는 둥근 괄호 안에 있는 0에서 9까지의 숫자를 추출하며, 

"+"는 이 패턴에서 하나 이상의 숫자를 추출합니다.

[0-9-]+는 문자열에서 나머지 숫자를 추출합니다. 

두 번째 "-"는 전화번호의 두 부분을 구분하는 대시를 나타내고, 

"+"는 문자열에 하나 이상의 숫자가 포함된 경우 Excel에게 하나 이상의 숫자를 추출하고 싶다고 말합니다.

열 A에 있는 각 셀에 이 패턴의 인스턴스가 하나뿐이므로 더 이상 인수를 추가할 필요가 없습니다. 

 

다시 한 번, 이 공식이 예상 결과를 생성한다는 것을 확인한 후에는 

채우기 핸들을 사용하여 열 D의 나머지 셀에 복제할 수 있습니다.

Excel에서 데이터를 추출하고 유사한 결과를 얻을 수 있는 다른 방법들이 있습니다. 

예를 들어, TIXTPLIT 함수나 Excel의 Flash Fill 도구를 사용하는 것이 있습니다.

 

REGEXPLACE로 데이터 조작

이 함수는 셀에서 텍스트를 받아 다른 셀에서 해당 데이터의 새 버전을 생성합니다. 

함수의 이름은 REGEXREAPEL이지만, 원래 위치의 원본 텍스트를 실제로 대체하지는 않습니다.

REGEXREPLACE(h,i,j,k,l)

 

h (required)는 대체하려는 텍스트를 포함하는 텍스트, 값 또는 셀 참조입니다.

i (required)는 교체하려는 패턴입니다.

j (required)는 당신이 만들고자 하는 대체물입니다.

k (선택 사항)는 교체하려는 패턴의 발생입니다.

l(선택 사항)은 대소문자 구분을 원하는 경우 0이거나, 그렇지 않은 경우 1입니다.

 

REGEXPLACE 사용 방법 예시

아래에서 열 A에 있는 이름 목록을 볼 수 있습니다. 

 

제 목표는 이 이름들을 열 B에 다시 만드는 것이지만, 

이름을 구분하는 쉼표를 포함한 "성, 이름" 형식을 사용하는 것입니다.

 

셀 B2에서는 다음과 같이 입력합니다:

=REGEXREPLACE([@Client name],"([A-Z][a-z]+) ([A-Z][a-z]+)","$2, $1")

 

[@클라이언트 이름]은 제가 영향을 미치고 싶은 데이터가 포함된 열을 참조합니다.

[A-Z][a-z]+가 공식에 두 번 포함되고 공백으로 구분되는 것은 

엑셀에게 대문자와 하나 이상의 소문자가 포함된 두 문자열의 텍스트를 가져가고 싶다고 말합니다.

$2, $1은 Excel에게 쉼표와 공백으로 구분된 두 문자열의 순서를 되돌리고 싶다고 말합니다. 

달러 기호를 포함하지 않으면 엑셀은 각 셀에 결과적으로 "2, 1"을 반환할 것입니다.

 

위 공식에서 인수 k와 l을 다루지 않은 이유는 Excel이 모든 사건(인수 k의 기본값)을 대체하고, 

인수 l의 기본값을 대소문자 구분하여 대체하기를 원하기 때문입니다.

형식이 지정된 테이블을 사용하고 있기 때문에 Enter 키를 누르면 열 B에 있는 나머지 셀에 공식이 적용됩니다.

 

정규 표현식은 엑셀에서만 사용되는 것이 아닙니다. 

 

실제로 REGEX를 사용하여 복사된 PDF 텍스트 수정, 다운로드한 파일의 대량 이름 변경, 

통화 형식 지정, HTML 태그 제거 등과 같은 컴퓨터의 다른 작업을 자동화할 수 있습니다.

 

 

 

2024.06.03-손상된 Excel 파일을 쉽게 복구할 수 있는 방법을 알아보자

손상된 Excel 파일을 쉽게 복구할 수 있는 방법을 알아보자

 

2024.04.08-Mac에서 스크롤 스크린샷을 찍는 4가지 방법 알아보기

2024.06.30-3D CAD 작성 시 가장 유용한 9개의 명령어 알아보기

3D CAD 작성 시 가장 유용한 9개의 명령어 알아보기

 

2024.12.31-Windows 11 작업 표시줄 숨기기(필요할 때까지)

 

-