[regex] 셀 내 및 루프 모두에서 Microsoft Excel에서 정규식 (Regex)을 사용하는 방법

정규식 은 패턴 일치에 사용됩니다.

Excel에서 사용하려면 다음 단계를 수행하십시오.

1 단계 : “Microsoft VBScript 정규식 5.5″에 VBA 참조 추가

• “개발자”탭을 선택하십시오 ( 이 탭이 없으면 어떻게해야합니까? )
• ‘코드’리본 섹션에서 “Visual Basic”아이콘을 선택하십시오.
• “Microsoft Visual Basic for Applications”창의 상단 메뉴에서 “도구”를 선택하십시오.
• “참조”를 선택하십시오
• 통합 문서에 포함 시키려면 “Microsoft VBScript Regular Expressions 5.5″옆의 확인란을 선택하십시오.
• “확인”을 클릭하십시오

2 단계 : 패턴 정의

기본 정의 :

- 범위.

• 예를 들어 a-za에서 z까지의 소문자와 일치
• 예 : 0-50에서 5까지의 숫자와 일치

[] 이 괄호 안의 객체 중 하나와 정확히 일치하십시오.

• 예 [a]를 들어 문자 a와 일치
• 예 : [abc]a, b 또는 c 일 수있는 단일 문자와 일치
• 예 [a-z]를 들어 알파벳의 소문자 하나와 일치합니다.

()반품 목적으로 다른 경기를 그룹화합니다. 아래 예를 참조하십시오.

{} 이전에 정의 된 패턴의 반복 사본에 대한 승수입니다.

• 예를 들어 [a]{2}두 개의 연속 된 소문자 a와 일치합니다.aa
• 예는 [a]{1,3}적어도 하나의 최대 세 개의 소문자와 일치 a, aa,aaa

+ 이전에 정의 된 패턴 중 하나 이상을 일치시킵니다.

• 예는 a+일치 연속 A의 a, aa, aaa, 등

? 이전에 정의 된 패턴 중 하나 또는 하나를 일치시킵니다.

• 예를 들어 패턴은 존재하거나 존재하지 않을 수 있지만 한 번만 일치 될 수 있습니다.
• 예를 들어 [a-z]?빈 문자열이나 소문자 하나와 일치합니다.

* 이전에 정의 된 패턴 중 0 개 이상을 일치시킵니다. -존재하거나 존재하지 않을 수있는 패턴을위한 와일드 카드. -예 : [a-z]*빈 문자열 또는 소문자 문자열과 일치합니다.

. 개행을 제외한 모든 문자와 일치 \n

• 예 : a.a로 시작하고 다음을 제외한 모든 것으로 끝나는 두 문자열을 찾습니다.\n

| OR 연산자

• 예를 들어 또는 일치 할 수 있음을 a|b의미합니다 .ab
• 예 red|white|orange를 들어 색상 중 하나와 정확히 일치합니다.

^ NOT 연산자

• 예를 들어 [^0-9]문자는 숫자를 포함 할 수 없습니다
• 예를 들어 [^aA]문자는 소문자 a또는 대문자 일 수 없습니다A

\ 뒤에 나오는 특수 문자를 이스케이프 처리합니다 (위의 동작을 무시 함)

• 예 \., \\, \(, \?, \$,\^

고정 패턴 :

^ 문자열 시작시 일치해야합니다

• 예 : ^a첫 문자는 소문자 여야합니다a
• 예를 들어 ^[0-9]첫 문자는 숫자 여야합니다.

$ 문자열 끝에서 일치해야합니다.

• 예 : a$마지막 문자는 소문자 여야합니다a

우선 순위 테이블 :

Order  Name                Representation
1      Parentheses         ( )
2      Multipliers         ? + * {m,n} {m, n}?
3      Sequence & Anchors  abc ^ $
4      Alternation         |

사전 정의 된 문자 약어 :

abr    same as       meaning
\d     [0-9]         Any single digit
\D     [^0-9]        Any single character that's not a digit
\w     [a-zA-Z0-9_]  Any word character
\W     [^a-zA-Z0-9_] Any non-word character
\s     [ \r\t\n\f]   Any space character
\S     [^ \r\t\n\f]  Any non-space character
\n     [\n]          New line

예 1 : 매크로로 실행

다음 예제 매크로는 셀의 값 A1을보고 처음 1 또는 2 문자가 숫자인지 확인합니다. 그렇다면 제거되고 나머지 문자열이 표시됩니다. 그렇지 않은 경우 일치하는 항목이 없다는 내용의 상자가 나타납니다. 숫자가 문자열의 시작 부분에 없었기 때문에 셀 A1값 12abc은 return abc, 값 1abc은 return abc, abc123“Not Matched”를 반환합니다.

Private Sub simpleRegex()
    Dim strPattern As String: strPattern = "^[0-9]{1,2}"
    Dim strReplace As String: strReplace = ""
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1")

    If strPattern <> "" Then
        strInput = Myrange.Value

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.Test(strInput) Then
            MsgBox (regEx.Replace(strInput, strReplace))
        Else
            MsgBox ("Not matched")
        End If
    End If
End Sub

예 2 : 셀 내 함수로 실행

이 예는 예 1과 동일하지만 셀 내 기능으로 실행되도록 설정되어 있습니다. 사용하려면 코드를 다음과 같이 변경하십시오.

Function simpleCellRegex(Myrange As Range) As String
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String


    strPattern = "^[0-9]{1,3}"

    If strPattern <> "" Then
        strInput = Myrange.Value
        strReplace = ""

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.test(strInput) Then
            simpleCellRegex = regEx.Replace(strInput, strReplace)
        Else
            simpleCellRegex = "Not matched"
        End If
    End If
End Function

cell에 문자열 ( “12abc”)을 배치하십시오 A1. 이 수식 =simpleCellRegex(A1)을 셀에 입력 B1하면 결과는 “abc”가됩니다.

예 3 : 루프 스루 범위

이 예제는 예제 1과 동일하지만 셀 범위를 반복합니다.

Private Sub simpleRegex()
    Dim strPattern As String: strPattern = "^[0-9]{1,2}"
    Dim strReplace As String: strReplace = ""
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1:A5")

    For Each cell In Myrange
        If strPattern <> "" Then
            strInput = cell.Value

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            If regEx.Test(strInput) Then
                MsgBox (regEx.Replace(strInput, strReplace))
            Else
                MsgBox ("Not matched")
            End If
        End If
    Next
End Sub

예 4 : 다른 패턴을 분리

이 예제는 범위 ( A1, A2& A3) 를 반복 하며 3 자리 숫자로 시작하고 단일 알파벳 문자와 4 자리 숫자로 이루어진 문자열을 찾습니다. 출력은를 사용하여 패턴 일치를 인접한 셀로 분리합니다 (). $1의 첫 번째 세트 내에서 일치하는 첫 번째 패턴을 나타냅니다 ().

Private Sub splitUpRegexPattern()
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1:A3")

    For Each C In Myrange
        strPattern = "(^[0-9]{3})([a-zA-Z])([0-9]{4})"

        If strPattern <> "" Then
            strInput = C.Value

            With regEx
                .Global = True
                .MultiLine = True
                .IgnoreCase = False
                .Pattern = strPattern
            End With

            If regEx.test(strInput) Then
                C.Offset(0, 1) = regEx.Replace(strInput, "$1")
                C.Offset(0, 2) = regEx.Replace(strInput, "$2")
                C.Offset(0, 3) = regEx.Replace(strInput, "$3")
            Else
                C.Offset(0, 1) = "(Not matched)"
            End If
        End If
    Next
End Sub

결과 :

추가 패턴 예

String   Regex Pattern                  Explanation
a1aaa    [a-zA-Z][0-9][a-zA-Z]{3}       Single alpha, single digit, three alpha characters
a1aaa    [a-zA-Z]?[0-9][a-zA-Z]{3}      May or may not have preceding alpha character
a1aaa    [a-zA-Z][0-9][a-zA-Z]{0,3}     Single alpha, single digit, 0 to 3 alpha characters
a1aaa    [a-zA-Z][0-9][a-zA-Z]*         Single alpha, single digit, followed by any number of alpha characters

</i8>    \<\/[a-zA-Z][0-9]\>            Exact non-word character except any single alpha followed by any single digit

답변

=regex("Peter Gordon: some@email.com, 47", "\w+@\w+\.\w+")
=regex("Peter Gordon: some@email.com, 47", "\w+@\w+\.\w+", "$0")

=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")

=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: some@email.com, 47", "^(.+): (.+), (\d+)$", "$" & 2)

Function RegParse(ByVal pattern As String, ByVal html As String)
    Dim regex   As RegExp
    Set regex = New RegExp

    With regex
        .IgnoreCase = True  'ignoring cases while regex engine performs the search.
        .pattern = pattern  'declaring regex pattern.
        .Global = False     'restricting regex to find only first match.

        If .Test(html) Then         'Testing if the pattern matches or not
            mStr = .Execute(html)(0)        '.Execute(html)(0) will provide the String which matches with Regex
            RegParse = .Replace(mStr, "$1") '.Replace function will replace the String with whatever is in the first set of braces - $1.
        Else
            RegParse = "#N/A"
        End If

    End With
End Function

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

=IF(REGEXMATCH(E8,"MiB"),REGEXEXTRACT(E8,"\d*\.\d*|\d*")/1000,IF(REGEXMATCH(E8,"GiB"),REGEXEXTRACT(E8,"\d*\.\d*|\d*"),"")

=regex_subst("watermellon", "[aeiou]", "")
---> wtrmlln
=regex_subst("watermellon", "[^aeiou]", "")
---> aeeo

Function regex_subst( _
     strInput As String _
   , matchPattern As String _
   , Optional ByVal replacePattern As String = "" _
) As Variant
    Dim inputRegexObj As New VBScript_RegExp_55.RegExp

    With inputRegexObj
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = matchPattern
    End With

    regex_subst = inputRegexObj.Replace(strInput, replacePattern)
End Function