Excel의 정규식 함수를 사용하여 검색을 강화하는 방법

Excel에서 필터링하고 검색하는 것은 간단한 작업이 아닙니다. 정규식 함수가 그것을 바꿀 것입니다. 이제 복잡한 패턴, 부분 일치, 구조화된 데이터 추출 등 필요한 것을 별다른 노력 없이 정확하게 지정할 수 있습니다.

정규식이란 무엇인가요?
패턴 검색
정규식을 사용한 추가 데이터
정규 표현식으로 찾기 및 바꾸기
정규식을 다른 함수와 결합하기

정규식이란 무엇인가요?

정규식은 텍스트나 문자열에서 일치 항목을 검색하는 데 사용되는 패턴 유형입니다. 웹사이트에서 로그인 페이지에 입력한 이메일 템플릿이 유효하지 않다고 알려주는 경우가 있는지 궁금해하신 적이 있나요? 다음은 이메일 서명을 활용한 정규식 패턴의 예입니다.

정규 표현식은 Excel에서만 사용할 수 있는 것이 아닙니다. 다양한 텍스트 편집기, 프로그래밍 언어, 명령줄 도구, IDE는 물론 Excel의 경쟁자인 Google 시트 에서도 사용할 수 있습니다 .

정규 표현식은 복잡해 보일 수 있고, 최대한 활용하려면 복잡할 수도 있지만, 효과적으로 사용하려면 프로그래머가 될 필요는 없습니다. 어떤 경우에는 몇 가지 기본적인 기호와 패턴을 사용하는 방법만 알아도 됩니다. 이 가이드는 최대한 간단하게 작성되어 바로 사용할 수 있도록 하였습니다.

이 가이드에서 사용되는 기호는 다음과 같습니다.

상징	설명하다
-	괄호 안에 문자 범위를 지정하세요.
^	문자열의 시작 부분과 일치합니다.
$	문자열의 끝과 일치합니다.
.	줄바꿈 문자를 제외한 모든 문자와 일치합니다.
*	0개 이상의 선행 문자와 일치합니다.
+	이전 문자나 문자들과 일치합니다.
()	일치하는 문자를 하나로 그룹화합니다.
[]	괄호 안의 모든 문자와 일치합니다.
[^]	괄호에 없는 모든 문자와 일치합니다.
{N}	이전 문자의 정확히 n개 인스턴스와 일치합니다.
{N,}	이전 문자의 n개 이상 발생과 일치합니다.

이러한 기호를 사용하여 작성할 수 있는 간단한 정규 표현식 패턴은 다음과 같습니다.

정규식 패턴	설명하다
[0-9]	0~9까지의 숫자와 일치합니다.
[a-zA-z0-9]	이는 소문자 a~z, 대문자 A~Z, 0~9의 단일 문자와 일치하는 일치 범위입니다.
^프로	pro 로 시작하는 모든 문자열과 일치합니다 .
[^$]	$ 이외의 모든 문자와 일치합니다 .
(어린이)	하위 그룹 샘플 .
a{3,}	a 뒤에 나오는 부분이 3개 이상 나타나는 경우와 일치합니다 (예: a , aa , aaa ).

정규식 함수는 텍스트 문자열을 검색하고 조작하기 위한 패턴을 정의하는 데 사용할 수 있는 미리 정의된 Excel 수식입니다. 현재 정규식 함수는 3개 있습니다. 이들을 별도로 사용하는 방법과 다른 기능과 함께 사용하는 방법을 살펴보겠습니다.

패턴 검색

우리가 살펴볼 첫 번째 함수는 REGEXTEST입니다. 이 함수는 검색에 사용할 텍스트 문자열과 정규식 패턴을 받은 다음, 후자의 패턴을 사용하여 전자의 패턴에서 일치 항목을 찾습니다. 이 함수는 True 또는 False를 반환합니다.

REGEXTEST 함수의 구문은 다음과 같습니다.

REGEXTEST(string_to_search, regex_pattern_to_use, [case_senstivity])

처음 두 매개변수인 string_to_search 와 regex_pattern_to_use는 설명이 필요 없을 정도로 명확합니다. [case_sensitivity] 매개변수는 선택 사항입니다. Excel 구문에 대해 설명할 때 대괄호로 묶인 모든 내용은 선택 사항이며, 검색에서 대소문자를 구분할지(0) 또는 구분하지 않을지(1)를 나타냅니다. 기본적으로 대소문자를 구분합니다.

이 예제에서는 다음 공식을 사용하여 사용자가 유효한 이메일 주소를 입력했는지 확인하기 위해 REGEXTEST를 사용합니다.

REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$")

여기서는 아래 정규 표현식 패턴을 사용하여 셀 B3에 이메일 주소가 포함되어 있는지 확인합니다.

^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$

셀 C3에 수식을 입력하고 셀 B3에 [email protected]을 입력하면 수식이 이메일 서명과 일치하므로 True가 반환됩니다.

Excel의 정규식 함수를 사용하여 검색을 강화하는 방법 — Excel의 REGEXTEST 함수.

정규식을 사용한 추가 데이터

다음으로 REXEXEXTRACT 함수를 살펴보겠습니다. 이 함수는 제공된 정규식 패턴과 일치하는 하위 문자열(문자열의 일부)을 반환합니다.

REXEXEXTRACT 함수의 구문은 다음과 같습니다.

REGEXEXTRACT(string_to_search, regex_pattern_to_use, [return_mode], [case_senstivity])

이메일 예제를 계속해서, 이메일 부분의 사용자 이름을 추출하는 수식을 셀 B4에 추가해 보겠습니다.

수식은 다음과 같습니다.

=REGEXEXTRACT(B3, "([^@]+)")

이 공식에서는 B3에 입력된 이메일 주소에서 @ 기호 앞에 있는 모든 내용을 추출합니다.

정규 표현식으로 찾기 및 바꾸기

우리가 살펴볼 마지막 정규식 함수는 REGEXREPLACE입니다. 이 함수는 Excel의 REPLACE 함수와 유사하지만 RegEx도 지원합니다. 수정하려는 텍스트 문자열을 가져와서 정의된 정규식 패턴과 일치하는 하위 문자열이 있는지 확인합니다. 찾은 경우 해당 문자열을 제공된 대체 문자열로 바꿉니다.

REGEXREPLACE 함수의 구문은 다음과 같습니다.

REGEXREPLACE(string_to_modify, regex_pattern_to_use, replacement_string, [number_of_occurrences], [case_senstivity])

이 함수에서 주목해야 할 중요한 매개변수는 다음과 같습니다.

string_to_modify : 수정하려는 텍스트 문자열입니다.
replacement_string : 하위 문자열을 대체할 문자열입니다.
number_of_occurrences : 바꾸고자 하는 정확한 인스턴스입니다.

다음은 이메일의 사용자 이름 부분을 다른 텍스트 문자열로 바꾸는 함수를 사용하는 예입니다.

=REGEXREPLACE(B3, "^[^@]+", "jane.doe")

B3의 값은 [email protected] 이고 , 위 수식을 C3 셀에 입력하면 [email protected]이 반환됩니다.

정규식을 다른 함수와 결합하기

Excel에서는 정규식 함수를 다른 함수와 결합할 수도 있습니다. 예를 들어, REGEXTEST 함수를 Excel의 IF 문과 결합하여 결과에 따라 적절한 메시지를 표시할 수 있습니다.

다음은 수식의 예입니다.

=IF(REGEXTEST(B3, "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$"), "This is a valid email address!", "The email address is invalid!")

이 수식은 IF 문을 사용하여 셀 B6에 입력된 이메일 주소가 유효한지 확인한 후 "유효한 이메일 주소입니다!"라고 표시합니다. TRUE 이면 이메일 주소가 잘못되었습니다 ! FALSE 이면 . 또한, 이 수식을 FIND 함수 와 함께 사용하면 Excel에서 빠르게 데이터를 찾을 수 있습니다.