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

regex excel vba


Excel에서 정규식을 사용하고 데이터 조작을 위해 Excel의 강력한 그리드와 같은 설정을 활용하려면 어떻게해야합니까?

  • 일치하는 패턴 또는 대체 된 값을 문자열로 반환하는 셀 내 함수입니다.
  • Sub는 데이터 열을 반복하고 인접한 셀에 일치하는 항목을 추출합니다.
  • 어떤 설정이 필요합니까?
  • 정규식에 대한 Excel의 특수 문자는 무엇입니까?

나는 엑셀이 비슷한 조작을 위해 Left , Mid , Right , Instr 유형 명령을 사용할 수 있기 때문에 Regex가 많은 상황에 이상적이지 않다는 것을 이해합니다 ( 정규 표현식을 사용하거나 사용하지 않으려면? ) .





Answer 1 Portland Runner


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

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

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

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

2 단계 : 패턴 정의

기본 정의 :

- 범위.

  • 예를 들어 a-z 는 a에서 z까지 소문자와 일치합니다.
  • 예를 들어 0-5 는 0에서 5까지의 숫자와 일치합니다.

[] 이 괄호 안의 객체 중 하나와 정확히 일치합니다.

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

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

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

  • 예는 [a]{2} 두 개의 연속적인 소문자 a를 일치 : aa
  • 예 : [a]{1,3} 은 소문자 a , aa , aaa 중 적어도 하나와 최대 3 개까지 일치합니다

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

  • 예를 들어 a+ 는 연속적인 a , aa , aaa 등과 일치 합니다.

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

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

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

. 줄 바꿈을 제외한 모든 문자와 일치합니다. \n

  • 예 : a. a로 시작하고 \n 이외의 것으로 끝나는 두 문자열을 찾습니다.

| OR 연산자

  • 예를 들어 a|ba 또는 b 가 일치 될 수 있음을 의미합니다 .
  • 예를 들어 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 는 반환 abc 의 가치, 1abc 이 반환됩니다 abc 의 값 abc123 숫자는 문자열의 시작에 있지 않았기 때문에 '일치하지 않음 "을 반환합니다.

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

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

results image


예 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

Results:

results image


추가 패턴 예

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



Answer 2 Patrick Böker


Excel 수식에서 직접 정규식을 사용하려면 다음 UDF (사용자 정의 함수)가 도움이 될 수 있습니다. 그것은 정규 표현식 기능을 엑셀 함수로 거의 직접적으로 노출시킵니다.

작동 원리

2-3 개의 매개 변수가 필요합니다.

  1. 정규식을 사용할 텍스트입니다.
  2. 정규식.
  3. 결과의 모양을 지정하는 형식 문자열입니다. $0 , $1 , $2 등을 포함 할 수 있습니다 . $0 은 전체 일치, $1 이상은 정규식의 각 일치 그룹에 해당합니다. 기본값은 $0 입니다.

몇 가지 예

이메일 주소 추출하기 :

=regex("Peter Gordon: [email protected], 47", "\[email protected]\w+\.\w+")
=regex("Peter Gordon: [email protected], 47", "\[email protected]\w+\.\w+", "$0")

결과 : [email protected]

여러 부분 문자열 추출하기 :

=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "E-Mail: $2, Name: $1")

결과 : E-Mail: [email protected], Name: Peter Gordon

단일 셀의 결합 된 문자열을 여러 셀의 구성 요소로 분리하려면 다음을 수행하십시오.

=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 1)
=regex("Peter Gordon: [email protected], 47", "^(.+): (.+), (\d+)$", "$" & 2)

결과 : Peter Gordon [email protected] ...

사용하는 방법

이 UDF를 사용하려면 다음을 수행하십시오 (대략 이 Microsoft 페이지를 기반으로 합니다 . 추가 정보가 있습니다).

  1. Excel에서 매크로 사용 파일 ( '.xlsm')의 ALT+F11 을 눌러 Microsoft Visual Basic for Applications Editor 를 엽니 다 .
  2. VBA 참조를 정규식 라이브러리에 추가하십시오 ( Portland Runners ++ answer 에서 뻔뻔스럽게 복사 됨 ).
    1. 도구 -> 참조를 클릭하십시오 (독일 스크린 샷을 실례하십시오)Tools -> References
    2. 목록에서 Microsoft VBScript Regular Expressions 5.5 를 찾아 옆에있는 확인란을 선택하십시오.
    3. 확인을 클릭하십시오 .
  3. 모듈 삽입을 클릭하십시오 . 모듈에 다른 이름을 지정하면 모듈의 이름이 아래 UDF와 동일 하지 않아야 합니다 (예 : 모듈 Regex 이름 지정 및 regex 함수 이름 지정 은 #NAME! 오류를 발생시킵니다).

    Second icon in the icon row -> Module

  4. 가운데의 큰 텍스트 창에서 다음을 삽입하십시오.

    Function regex(strInput As String, matchPattern As String, Optional ByVal outputPattern As String = "$0") As Variant
        Dim inputRegexObj As New VBScript_RegExp_55.RegExp, outputRegexObj As New VBScript_RegExp_55.RegExp, outReplaceRegexObj As New VBScript_RegExp_55.RegExp
        Dim inputMatches As Object, replaceMatches As Object, replaceMatch As Object
        Dim replaceNumber As Integer
    
        With inputRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = matchPattern
        End With
        With outputRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = "\$(\d+)"
        End With
        With outReplaceRegexObj
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
        End With
    
        Set inputMatches = inputRegexObj.Execute(strInput)
        If inputMatches.Count = 0 Then
            regex = False
        Else
            Set replaceMatches = outputRegexObj.Execute(outputPattern)
            For Each replaceMatch In replaceMatches
                replaceNumber = replaceMatch.SubMatches(0)
                outReplaceRegexObj.Pattern = "\$" & replaceNumber
    
                If replaceNumber = 0 Then
                    outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).Value)
                Else
                    If replaceNumber > inputMatches(0).SubMatches.Count Then
                        'regex = "A to high $ tag found. Largest allowed is $" & inputMatches(0).SubMatches.Count & "."
                        regex = CVErr(xlErrValue)
                        Exit Function
                    Else
                        outputPattern = outReplaceRegexObj.Replace(outputPattern, inputMatches(0).SubMatches(replaceNumber - 1))
                    End If
                End If
            Next
            regex = outputPattern
        End If
    End Function
  5. Microsoft Visual Basic for Applications Editor 창 저장하고 닫습니다 .




Answer 3 SAm


서두르는 사람들을 위해 patszim 의 답변 을 확장하십시오 .

  1. Excel 통합 문서를 엽니 다.
  2. Alt+F11VBA / 매크로 창을 엽니 다.
  3. 아래 정규식에 대한 참조를 추가 도구 다음 참조
    ![Excel VBA Form add references
  4. Microsoft VBScript Regular Expression 5.5 선택
    ![Excel VBA add regex reference
  5. 새 모듈을 삽입하십시오 (코드는 모듈에 상주해야하며 그렇지 않으면 작동하지 않습니다).
    ![Excel VBA insert code module
  6. 새로 삽입 된 모듈에서
    ![Excel VBA insert code into module
  7. 다음 코드를 추가하십시오.

    Function RegxFunc(strInput As String, regexPattern As String) As String
        Dim regEx As New RegExp
        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .pattern = regexPattern
        End With
    
        If regEx.Test(strInput) Then
            Set matches = regEx.Execute(strInput)
            RegxFunc = matches(0).Value
        Else
            RegxFunc = "not matched"
        End If
    End Function
  8. ![Excel regex function in-cell usage정규식 패턴은 셀 중 하나에 배치되고 절대 참조 가 사용됩니다. 함수는 작성된 통합 문서에 연결됩니다.
    다른 통합 문서에서 사용해야 할 경우에는 Personal.XLSB에 함수를 저장하십시오.




Answer 4 Vikas Gautam


내 시도는 다음과 같습니다.

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



Answer 5 DeezCashews


이것을 SUM 또는 VLOOKUP 과 같은 셀 함수로 사용해야하고 다음과 같은 것이 쉽다는 것을 알았습니다.

  1. 매크로 사용 Excel 파일 (xlsm로 저장)에 있는지 확인하십시오.
  2. 개방형 개발자 도구Alt+F11
  3. 다른 답변과 마찬가지로 Microsoft VBScript Regular Expressions 5.5 추가
  4. 통합 문서 또는 자체 모듈에서 다음 함수를 작성하십시오.

    Function REGPLACE(myRange As Range, matchPattern As String, outputPattern As String) As Variant
        Dim regex As New VBScript_RegExp_55.RegExp
        Dim strInput As String
    
        strInput = myRange.Value
    
        With regex
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = matchPattern
        End With
    
        REGPLACE = regex.Replace(strInput, outputPattern)
    
    End Function
  5. 그런 다음 =REGPLACE(B1, "(\w) (\d+)", "$1$2") 사용하여 셀에서 사용할 수 있습니다 (예 : "A 243"- "A243")