Excel Regex 예 : 공식에서 정규 표현식 사용

Excel 공식에서 정규 표현이 지원되지 않는 이유를 결코 이해할 수 없습니까? 이제 우리의 사용자 정의 기능을 사용하면 특정 패턴과 일치하는 문자열을 쉽게 찾고 교체, 추출 및 제거 할 수 있습니다.

첫눈에 Excel에는 텍스트 문자열 조작에 필요한 모든 것이 있습니다. 흠… 정규 표현은 어떻습니까? 죄송합니다. Excel에는 내장 된 REGEX 기능이 없습니다. 그러나 아무도 우리가 우리 자신의 것을 만들 수 없다고 말하지 않습니다. :)

• REGEX 란 무엇입니까?
• Excel에 Regex 기능이 있습니까?
• Excel Regex 치트 시트
• 사용자 정의 VBA Regex 기능
• Regex 매치 함수
• REGEX 추출물 기능
• REGEX를 대체합니다
• Regex Tools- VBA없이 Excel의 정기적 인 표현

정규 표현이란 무엇입니까?

정규 표현식 (일명 Regex 또는 Regexp )은 검색 패턴을 정의하는 특수 코딩 된 문자 순서입니다. 이 패턴을 사용하면 문자열에서 일치하는 문자 조합을 찾거나 데이터 입력을 확인할 수 있습니다. 와일드 카드 표기법에 익숙하다면 Regexes를 고급 버전의 와일드 카드로 생각할 수 있습니다.

정규 표현에는 특수 문자, 연산자 및 구조로 구성된 자체 구문이 있습니다. 예를 들어, [0-5]는 단일 숫자와 0에서 5까지 일치합니다.

정규식은 JavaScript 및 VBA를 포함한 많은 프로그래밍 언어로 사용됩니다. 후자에는 특수 regexp 객체가 있으며 사용자 정의 기능을 만들기 위해 활용합니다.

Excel은 Regex를 지원합니까?

유감스럽게도 Excel에는 내장 된 Regex 기능이 없습니다. 공식에서 일반 표현식을 사용하려면 자신만의 사용자 정의 기능 (VBA 또는 .NET 기반)을 만들거나 Regexes를 지원하는 타사 도구를 설치해야합니다.

Excel Regex 치트 시트

Regex 패턴이 매우 간단하거나 매우 정교한 지 여부는 공통 구문을 사용하여 구축됩니다. 이 튜토리얼은 정기적 인 표현을 가르치는 것을 목표로하지 않습니다. 이를 위해 초보자를위한 무료 튜토리얼부터 고급 사용자를위한 프리미엄 코스에 이르기까지 온라인으로 많은 리소스가 있습니다.

아래에서는 기본 사항을 파악하는 데 도움이되는 주요 Regex 패턴에 대한 빠른 참조를 제공합니다. 추가 예제를 공부할 때 치트 시트로도 작동 할 수도 있습니다.

정규 표현식에 익숙하다면 Regexp 기능으로 바로 이동할 수 있습니다.

캐릭터

이것은 특정 문자와 일치하는 가장 자주 사용되는 패턴입니다.

무늬	설명	예	성냥
.	와일드 카드 문자 : 라인 브레이크를 제외한 단일 문자와 일치합니다.	.ot	도트 , 뜨거운 냄비 , @ot
\디	숫자 문자 : 0에서 9까지의 단일 숫자	\디	A1B 에서는 1 과 일치합니다
\디	숫자가 아닌 캐릭터	\디	A1B 에서 A 와 B와 일치합니다
\에스	공백 문자 : 공간, 탭, 새로운 라인 및 캐리지 리턴	.\에스.	3 센트로 , 3 c
\에스	비 윈 스페이스 캐릭터	\에스	30 센트로 30 센트 와 센트 와 일치합니다
\ w	단어 문자 : ASCII 문자, 숫자 또는 밑줄	\ w	5_cats *** 에서 5_cats 와 일치합니다
\ w	영숫자 또는 밑줄이 아닌 캐릭터	\ w	5_cats *** , 일치 ***
\티	꼬리표
\N	새로운 라인	\ n \ d	아래의 2 라인 문자열에서 10과 일치합니다 고양이 5 마리의 개
\	캐릭터의 특별한 의미를 빠져 나와서 검색 할 수 있습니다.	\. \ w \.	문자를 찾을 수 있도록 기간을 탈출합니다. 문자열의 캐릭터 Mr. , Mrs. , Prof.

캐릭터 클래스

이러한 패턴을 사용하면 다른 문자 세트의 요소와 일치 할 수 있습니다.

무늬	설명	예	성냥
[캐릭터]	브래킷의 단일 문자와 일치합니다	d [oi] g	개와 발굴
[^문자]	괄호 안에없는 단일 문자와 일치합니다	d [^oi] g	DAG, DUG , D1G 는 개와 발굴 과 일치하지 않습니다.
[부터]	괄호 사이의 범위의 모든 문자와 일치합니다	[0-9] [AZ] [AZ]	0에서 9까지의 단일 숫자 단일 소문자 문자 단일 대문자 문자

수량 자

수량자는 일치 할 문자 수를 지정하는 특수 표현입니다. 수량자는 항상 캐릭터에 적용됩니다.

무늬	설명	예	성냥
*	0 이상 발생합니다	1A*	1, 1A , 1AA, 1AAA 등
	하나 이상의 발생	포	냄비 에서 PO 와 일치하고 POO 와 일치합니다 .
?	0 또는 한 번 발생합니다	도로	도로,로드
??	0 개 이상의 발생이지만 가능한 한 적습니다	1A*?	1A , 1AA 및 1AAA 에서 1A 와 일치합니다
?	하나 이상의 발생이지만 가능한 한 적은 수치입니다	포?	냄비 와 가난한 곳 에서 Po 와 일치합니다
??	0 또는 하나의 발생이지만 가능한 한 적은 수치입니다	ROA ??	도로 와 막대 에서 Ro 와 일치합니다
{N}	앞의 패턴 n 번과 일치합니다	\ d {3}	정확히 3 자리
{N,}	앞의 패턴 n 이상과 일치합니다	\ d {3,}	3 자리 이상
{n, m}	이전 패턴과 N 시간과 M 회 사이의 패턴과 일치합니다	\ d {3,5}	3 ~ 5 자리

그룹화

그룹화 구조는 소스 문자열에서 서브 스트링을 캡처하는 데 사용되므로 일부 작업을 수행 할 수 있습니다.

통사론	설명	예	성냥
(무늬)	캡처 그룹 : 일치하는 부분 문자열을 캡처하고 서수 번호를 지정합니다.	(\ d)	5 마리의 고양이와 10 마리의 개에서 5 개 (그룹 1)와 10 (그룹 2)을 포착합니다.
(?:무늬)	비 캡처 그룹 : 그룹과 일치하지만 캡처하지 않습니다.	(\ d) (? : 개)	고양이 5 마리와 10 마리의 개에서 10 개를 포착합니다
\ 1	그룹 1의 내용	(\ d) \ (\ d) = \ 2 \ \ 1	5 10 = 10 5와 일치 하고 그룹을 캡처하는 5 및 10을 캡처합니다.
\ 2	그룹 2의 내용

앵커

앵커는 일치를 찾을 위치 입력 문자열의 위치를 ​​지정합니다.

닻	설명	예	성냥
^	문자열의 시작 참고 : [^내부 괄호]는 "NOT"을 의미합니다.	^\ d	문자열의 시작 부분에 여러 자리가 있습니다. 5 마리의 고양이와 10 마리의 개 에서 5 개 일치합니다
$	문자열의 끝	\ d $	문자열 끝에있는 숫자의 여러 숫자. 10 Plus 5에서 15는 15를, 15는 15를 일치시킵니다
\비	단어 경계	\ bjoy \ b	기쁨을 별도의 단어로 일치 시키지만 즐겁지 는 않습니다.
\비	단어 경계가 아닙니다	\ bjoy \ b	기쁨 은 즐겁고 별도의 단어는 아닙니다.

교대 (또는) 구성

Alternation Operand는 OR Logic을 활성화 하므로이 요소와 일치 할 수 있습니다.

건설하다	설명	예	성냥
|	수직 막대로 분리 된 단일 요소와 일치합니다	(S | SH) Ells	그녀는 바다 껍질을 판매하고, 매치 판매 및 껍질을 판매합니다

외관

룩 어라운드 구성은 다른 것을 따르거나 따르지 않거나 앞에 맞지 않는 것을 일치 시키려고 할 때 도움이됩니다. 이러한 표현은 때때로 실제 문자보다는 위치와 일치하기 때문에 "제로 폭 assertions"또는 "zero width match"라고도합니다.

메모. VBA Regex 풍미에서는 외관이 지원되지 않습니다.

무늬	설명	예	성냥
(? =)	긍정적 인 전망	x (? = y)	표현식 x와 y가 y 할 때 X와 일치
(?!)	부정적인 전망	x (?! y)	표현 X와 일치합니다.
(?	긍정적 인 외관	(?	y가 앞에있을 때 표현식 x와 일치
(?	부정적인 외모	(?	표현 X와 일치합니다.

이제 필수 요소를 알았으므로 실제 데이터에 대한 Regexes를 사용하여 문자열을 구문 분석하고 필요한 정보를 찾으십시오. 구문에 대한 자세한 내용이 필요한 경우 정규 표현 언어에 대한 Microsoft 안내서가 도움이 될 수 있습니다.

Excel에 대한 사용자 정의 Regex 함수

이미 언급했듯이 Microsoft Excel에는 내장 Regex 기능이 없습니다. 정규 표현식을 활성화하기 위해 세 가지 사용자 정의 VBA 기능 (일명 사용자 정의 함수)을 만들었습니다. 아래에 연결된 페이지 나 샘플 통합 문서에서 코드를 복사 한 다음 자신의 Excel 파일에 붙여 넣을 수 있습니다.

VBA Regexp 기능의 작동 방식

이 섹션에서는 내부 역학을 설명하며 백엔드에서 무슨 일이 일어나는지 정확히 알고 싶어하는 사람들에게 흥미로울 수 있습니다.

VBA에서 정규 표현식을 사용하려면 Regex 객체 참조 라이브러리를 활성화하거나 CreateObject 함수를 사용해야합니다. VBA 편집기에서 참조를 설정하는 데 어려움을 겪기 위해 후자의 접근 방식을 선택했습니다.

regexp 객체에는 4 개의 속성이 있습니다.

• 패턴 - 입력 문자열에서 일치하는 패턴 입니다.
• Global- 입력 문자열에서 모든 일치를 찾을 것인지 또는 첫 번째 일치를 찾을 것인지 제어합니다. 우리의 기능에서는 모든 경기를 얻는 것이 참으로 설정됩니다.
• 멀티 린 - 멀티 라인 문자열에서 또는 첫 번째 줄에서만 라인 브레이크를 가로 질러 패턴을 일치 시킬지 결정합니다. 우리의 코드에서는 모든 줄에서 검색하도록 true로 설정됩니다.
• 무시 카세 -정규 표현이 대소 문자에 민감하지 않거나 (기본값) 또는 대소 문자로 설정되는지 여부를 정의합니다 (true로 설정). 당사의 경우 옵션 Match_case 매개 변수를 구성하는 방법에 따라 다릅니다. 기본적으로 모든 함수는 대소 문자에 민감합니다 .

VBA regexp 제한

Excel VBA는 필수 Regex 패턴을 구현하지만 .NET, Perl, Java 및 기타 REGEX 엔진에서 사용할 수있는 많은 고급 기능이 없습니다. 예를 들어, VBA Regexp는 (? i) Case-Insensitive Matching 또는 (? M)과 같은 인라인 수정 자, LookBehinds, Posix 클래스와 같은 인라인 수정자를 지원하지 않습니다.

Excel Regex 매치 함수

regexpmatch 함수는 정규 표현식과 일치하는 텍스트에 대한 입력 문자열을 검색하고 일치가 발견되면 true를 반환합니다. 그렇지 않으면 false.

regexpmatch (텍스트, 패턴, [match_case])

어디:

• 텍스트 (필수) - 검색 할 하나 이상의 문자열.
• 패턴 (필수) - 일치하는 정규 표현.
• match_case (선택 사항) - 매치 유형. 참 또는 생략 - 사례에 민감한; 거짓 - 사례에 민감합니다

함수의 코드는 여기에 있습니다.

예 : 정규 표현식을 사용하여 문자열과 일치하는 방법

아래 데이터 세트에서 SKU 코드가 포함 된 항목을 식별한다고 가정하십시오.

각 SKU는 2 개의 대문자로 시작하고 하이픈과 4 자리로 시작한다는 점을 감안할 때 다음 표현식을 사용하여 일치 할 수 있습니다.

패턴 : \ b [az] {2}-\ d {4} \ b

여기서 [az] {2}는 A에서 z까지 2 개의 대문자를 의미하며 \ d {4}는 0에서 9까지의 4 자리를 의미합니다. 단어 경계 \ b는 Sku가 별도의 단어이며 더 큰 문자열의 일부가 아니라는 것을 나타냅니다.

패턴이 설정되면 평소와 같이 공식을 입력하기 시작하면 Excel의 AutoComplete가 제안한 목록에 기능의 이름이 나타납니다.

원래 문자열이 A5에 있다고 가정하면 공식은 다음과 같습니다.

=RegExpMatch(A5, "\b[AZ]{2}-\d{3}\b")

편의를 위해, 정규 표현식을 별도의 셀에 입력하고 패턴 인수에 절대 참조 ($ 2)를 사용할 수 있습니다. 이렇게하면 공식을 다른 셀에 복사 할 때 셀 주소가 변경되지 않도록합니다.

=RegExpMatch(A5, $A$2)

True 및 False 대신 자신의 텍스트 레이블을 표시하려면 IF 함수에서 Nest RegexpMatch를 표시하고 value_if_true 및 value_if_false 인수에 원하는 텍스트를 지정하십시오.

=IF(RegExpMatch(A5, $A$2), "Yes", "No")

더 많은 공식 예제는 다음을 참조하십시오.

• 정규 표현식을 사용하여 문자열을 일치시키는 방법
• Regexes를 사용한 Excel 데이터 검증

Excel Regex Extract 기능

regexpextract 함수는 정규 표현식과 일치하는 하위 문자열을 검색하고 모든 일치 또는 특정 일치를 추출합니다.

regexpextract (텍스트, 패턴, [인스턴스_num], [match_case])

어디:

• 텍스트 (필수) - 검색 할 텍스트 문자열.
• 패턴 (필수) - 일치하는 정규 표현.
• instance_num (선택 사항) - 추출 할 인스턴스를 나타내는 일련 번호. 생략되면 모든 발견 된 매치를 반환합니다 (기본값).
• match_case (선택 사항) - 일치할지 (true 또는 생략) 또는 무시 (false) 텍스트 케이스를 정의합니다.

여기에서 함수 코드를 얻을 수 있습니다.

예 : 일반 표현식을 사용하여 문자열을 추출하는 방법

우리의 예제를 조금 더 가져 가면 송장 번호를 추출합시다. 이를 위해 우리는 7 자리 숫자와 일치하는 매우 간단한 정규식을 사용할 것입니다.

패턴 : \ b \ d {7} \ b

패턴을 A2에 넣으면이 작고 우아한 공식으로 작업을 완료하게됩니다.

=RegExpExtract(A5, $A$2)

패턴이 일치하면 공식은 송장 번호를 추출합니다. 일치하지 않으면 아무것도 반환되지 않습니다.

더 많은 예는 Regex를 사용하여 Excel에서 현을 추출하는 방법을 참조하십시오.

Excel Regex 대체 기능

regexpreplace 함수는 REGEX와 일치하는 값을 지정한 텍스트와 대체합니다.

regexpreplace (텍스트, 패턴, 교체, [instance_num], [match_case])

어디:

• 텍스트 (필수) - 검색 할 텍스트 문자열.
• 패턴 (필수) - 일치하는 정규 표현.
• 교체 (필수) - 일치하는 하위 문자를 대체하는 텍스트.
• instance_num (선택 사항) - 교체 할 인스턴스입니다. 기본값은 "모든 일치"입니다.
• match_case (선택 사항) - 일치할지 (참 또는 생략) 또는 무시 (false) 텍스트 케이스를 제어합니다.

기능 코드는 여기에서 사용할 수 있습니다.

예 : Regexes를 사용하여 문자열을 교체하거나 제거하는 방법

우리의 기록 중 일부에는 신용 카드 번호가 포함되어 있습니다. 이 정보는 기밀이므로 정보로 교체하거나 완전히 삭제할 수 있습니다. RegexPreplace 기능의 도움으로 두 작업 모두 수행 할 수 있습니다. 어떻게? 두 번째 시나리오에서는 빈 문자열로 교체 할 것입니다.

샘플 테이블에서 모든 카드 번호에는 16 자리가 있으며,이 자리는 공백으로 분리 된 4 개의 그룹으로 작성됩니다. 그것들을 찾기 위해, 우리는이 정규식을 사용하여 패턴을 복제합니다.

패턴 : \ b \ d {4} \ d {4} \ d {4} \ d {4} \ b

교체하려면 다음 문자열이 사용됩니다.

교체 : xxxx xxxx xxxx xxxx

다음은 신용 카드 번호를 무의미한 정보로 대체 할 수있는 완전한 공식입니다.

=RegExpReplace(A5, "\b\d{4} \d{4} \d{4} \d{4}\b", "XXXX XXXX XXXX XXXX")

별도의 셀 (A2 및 B2)의 REGEX 및 교체 텍스트를 사용하면 공식이 똑같이 잘 작동합니다.

Excel에서 "제거"는 "교체"의 특별한 경우입니다. 신용 카드 번호를 제거 하려면 교체 인수에 빈 문자열 ( "")을 사용하십시오.

=RegExpReplace(A5, "\b\d{4} \d{4} \d{4} \d{4}\b", "")

팁. 결과에서 빈 줄의 리그를 얻으려면이 예제에서 볼 수 있듯이 다른 regexpreplace 기능을 사용할 수 있습니다. Regex를 사용하여 빈 줄을 제거하는 방법.

자세한 내용은 다음을 참조하십시오.

• REGEX를 사용하여 Excel의 줄을 교체하는 방법
• Regex를 사용하여 문자열을 제거하는 방법
• Regexes를 사용하여 공백을 제거하는 방법

기판을 일치시키고 추출, 교체 및 제거하는 REGEX 도구

Ultimate Suite의 사용자는 통합 문서에 단일 코드 라인을 삽입하지 않고 정규식의 모든 힘을 얻을 수 있습니다. 필요한 모든 코드는 개발자가 작성하고 설치 중에 Excel에 Smoothy 통합됩니다.

위에서 논의한 VBA 기능과 달리 Ultimate Suite의 기능은 .NET 기반이며 두 가지 주요 장점을 제공합니다.

1. VBA 코드를 추가하고 거시적 가능 파일로 저장하지 않고 정규 표현식을 일반 .XLSX 통합 문서에서 사용할 수 있습니다.
2. .NET Regex 엔진은 완전한 기능을 갖춘 클래식 정규 표현식을 지원하므로보다 정교한 패턴을 구성 할 수 있습니다.

Excel에서 Regex를 사용하는 방법

Ultimate Suite가 설치된 상태에서 Excel에서 일반 표현식을 사용 하여이 두 단계만큼 간단합니다.

1. AbleBits 데이터 탭에서 텍스트 그룹의 Regex 도구를 클릭하십시오.

   

2. REGEX 도구 창에서 다음을 수행하십시오.
   • 소스 데이터를 선택하십시오.
   • REGEX 패턴을 입력하십시오.
   • 원하는 옵션 : 일치 , 추출 , 제거 또는 교체를 선택하십시오.
   • 결과를 값이 아닌 공식으로 얻으려면 삽입물을 공식 확인란으로 선택하십시오.
   • 작업 버튼을 누르십시오.

   예를 들어, 셀 A2 : A6에서 신용 카드 번호를 제거하려면 이러한 설정을 구성합니다.

   

트라이즈에서는 AbleBitsRegex 기능이 원래 데이터 오른쪽의 새 열에 삽입됩니다. 우리의 경우, 공식은 다음과 같습니다.

=AblebitsRegexRemove(A2, "\b\d{4} \d{4} \d{4} \d{4}\b")

공식이 있으면 기본 공식처럼 편집, 복사 또는 이동할 수 있습니다.

셀에 직접 정규식을 삽입하는 방법

AbleBitsRegex 기능은 애드 인의 인터페이스를 사용하지 않고 셀에 직접 삽입 할 수도 있습니다. 방법은 다음과 같습니다.

1. 공식 막대에서 FX 버튼을 클릭하거나 공식 탭에서 기능을 삽입하십시오 .
2. 함수 삽입 대화 상자에서 ablebitsudfs 카테고리를 선택하고 관심있는 기능을 선택하고 확인을 클릭하십시오.

   

3. 평소처럼 함수의 인수를 정의하고 확인을 클릭하십시오. 완료!

자세한 내용은 Excel을위한 Regex 도구를 참조하십시오.

이것이 엑셀 셀에서 텍스트를 일치시키고 추출, 교체하고 제거하기 위해 정규 표현식을 사용하는 방법입니다. 읽어 주셔서 감사합니다. 다음 주에 우리 블로그에서 뵙기를 기대합니다!

사용 가능한 다운로드

Excel Regex- 포뮬러 예제 (.xlsm 파일) Ultimate Suite- 시험판 (.exe 파일)

위 내용은 Excel Regex 예 : 공식에서 정규 표현식 사용의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!