Vraag Hoe krijg ik regex-ondersteuning in Excel via een functie of een aangepaste functie?


Het lijkt erop dat regex (zoals in reguliere expressies) niet wordt ondersteund in Excel, behalve via VBA. Is dit het geval, en zo ja, zijn er dan nog "open source" aangepaste VBA-functies die regex ondersteunen. In dit geval ben ik op zoek om een ​​complex patroon binnen een string te extraheren, maar elke implementatie van een aangepaste VBA-functie die ondersteuning van regex binnen de functie zelf blootlegt, zou van nut zijn. Als u een semi-gerelateerde functie kent zoals de IS functie, voel aanvoelen om te reageren, hoewel ik echt op zoek ben naar een volledige reguliere expressie-implementatie die wordt blootgesteld via functies. Misschien zelfs open voor een beloning om de invoegtoepassing te gebruiken als de implementatie goed is.

Ook hoef ik alleen maar te zeggen dat ik Office 2010 op Windows 7 gebruik; heeft deze info toegevoegd na een antwoord dat een goede suggestie leek te zijn bleek niet te werken op Office 2010.

Als u vragen hebt, kunt u reageren.


20
2017-12-29 18:51


oorsprong


antwoorden:


Niets ingebouwd in Excel. VBScript heeft ingebouwde ondersteuning en kan worden gebeld via VBA. Meer informatie beschikbaar Hier. U kunt het object bellen met behulp van late binding in VBA. Ik heb een aantal functies opgenomen die ik onlangs heb samengesteld. Houd er rekening mee dat deze niet goed zijn getest en mogelijk enkele bugs bevatten, maar ze zijn vrij eenvoudig.

Dit zou je in ieder geval op weg moeten helpen:

'---------------------------------------------------------------------------------------vv
' Procedure : RegEx
' Author    : Mike
' Date      : 9/1/2010
' Purpose   : Perform a regular expression search on a string and return the first match
'               or the null string if no matches are found.
' Usage     : If Len(RegEx("\d{1,2}[/-]\d{1,2}[/-]\d{2,4}", txt)) = 0 Then MsgBox "No date in " & txt
'           : TheDate = RegEx("\d{1,2}[/-]\d{1,2}[/-]\d{2,4}", txt)
'           : CUSIP = Regex("[A-Za-z0-9]{8}[0-9]",txt)
'---------------------------------------------------------------------------------------
'^^
Function RegEx(Pattern As String, TextToSearch As String) As String 'vv
    Dim RE As Object, REMatches As Object

    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = False
        .Global = False
        .IgnoreCase = False
        .Pattern = Pattern
    End With

    Set REMatches = RE.Execute(TextToSearch)
    If REMatches.Count > 0 Then
        RegEx = REMatches(0)
    Else
        RegEx = vbNullString
    End If
End Function '^^

'---------------------------------------------------------------------------------------
' Procedure : RegExReplace
' Author    : Mike
' Date      : 11/4/2010
' Purpose   : Attempts to replace text in the TextToSearch with text and back references
'               from the ReplacePattern for any matches found using SearchPattern.
' Notes     - If no matches are found, TextToSearch is returned unaltered.  To get
'               specific info from a string, use RegExExtract instead.
' Usage     : ?RegExReplace("(.*)(\d{3})[\)\s.-](\d{3})[\s.-](\d{4})(.*)", "My phone # is 570.555.1234.", "$1($2)$3-$4$5")
'             My phone # is (570)555-1234.
'---------------------------------------------------------------------------------------
'
Function RegExReplace(SearchPattern As String, TextToSearch As String, ReplacePattern As String, _
                      Optional GlobalReplace As Boolean = True, _
                      Optional IgnoreCase As Boolean = False, _
                      Optional MultiLine As Boolean = False) As String
Dim RE As Object

    Set RE = CreateObject("vbscript.regexp")
    With RE
        .MultiLine = MultiLine
        .Global = GlobalReplace
        .IgnoreCase = IgnoreCase
        .Pattern = SearchPattern
    End With

    RegExReplace = RE.Replace(TextToSearch, ReplacePattern)
End Function

'---------------------------------------------------------------------------------------
' Procedure : RegExExtract
' Author    : Mike
' Date      : 11/4/2010
' Purpose   : Extracts specific information from a string.  Returns empty string if not found.
' Usage     : ?RegExExtract("(.*)(\d{3})[\)\s.-](\d{3})[\s.-](\d{4})(.*)", "My phone # is 570.555.1234.", "$2$3$4")
'             5705551234
'             ?RegExExtract("(.*)(\d{3})[\)\s.-](\d{3})[\s.-](\d{4})(.*)", "My name is Mike.", "$2$3$4")
'
'             ?RegExReplace("(.*)(\d{3})[\)\s.-](\d{3})[\s.-](\d{4})(.*)", "My name is Mike.", "$2$3$4")
'             My name is Mike.
'---------------------------------------------------------------------------------------
'
Function RegExExtract(SearchPattern As String, TextToSearch As String, PatternToExtract As String, _
                      Optional GlobalReplace As Boolean = True, _
                      Optional IgnoreCase As Boolean = False, _
                      Optional MultiLine As Boolean = False) As String
Dim MatchFound As Boolean

    MatchFound = Len(RegEx(SearchPattern, TextToSearch)) > 0
    If MatchFound Then
        RegExExtract = RegExReplace(SearchPattern, TextToSearch, PatternToExtract, _
                                    GlobalReplace, IgnoreCase, MultiLine)
    Else
        RegExExtract = vbNullString
    End If
End Function

29
2017-12-29 19:15



Hier is een bericht over het gebruik van Regex in Excel:

http://mathfest.blogspot.com/2010/03/regular-expressions-in-excel.html

Hoop dat het helpt.

En een andere die Python en IronSpread gebruikt

http://mathfest.blogspot.ca/2012/06/using-ironspread-and-regular.html


9
2017-12-29 18:55



regexp-gebruik binnen functies is opgenomen in OpenOffice / LibreOffice Calc. Om te activeren, ga naar Tools> Opties> Calc> Calulate: Y = Reguliere expressies in formules inschakelen. Ik heb dit vele malen gebruikt.


7
2017-09-22 17:33



Ik heb een aantal oplossingen geprobeerd en gezien mijn gebrek aan expertise op VBA vond ik de meeste van hen te omslachtig voor mij. De makkelijkste die ik vond was SeoTools voor Excel (http://nielsbosma.se/projects/seotools/). Werkte als een charme voor mij.


2
2017-08-08 19:17



--- FEB 2014 ---

Gewoon om een ​​alternatief te geven, beide Open kantoor en Libre Office Calc-software (hun spreadsheet-softwarenaam) staat reguliere expressies toe in hun zoekfunctionaliteit.


1
2018-02-12 16:46



Ik had onlangs dezelfde exacte vraag en na het worstelen met het maken van mijn eigen tools en om ze correct te laten werken, vond ik een geweldige online ADDIN die erg gemakkelijk te gebruiken is.

Dit is het uittreksel van de maker

Voor mijn stage van de afgelopen maanden waarin ik heb gewerkt   de afdeling Marketingwetenschappen en een deel van mijn werk is krijgen   gegevens in MS Access en het genereren van rapporten. Dit gaat om krijgen   lijsten van prospects uit verschillende databronnen. Dit was meestal een   vrij eenvoudige prestatie met enkele standaard SQL-queries. Echter,   soms kreeg ik gegevens zoals adressen die niet overeenkwamen met die van andere   standaard formaat dat wordt gebruikt door IT. In het ergste geval werden de gegevens verstrekt in   een pdf wat betekende dat ik het alleen naar een niet-afgebakend tekstbestand kon exporteren.   Ik ontdekte dat ik echt een paar algemene reguliere expressie nodig had   functies om velden te ontleden om te importeren in MS Access. Ik heb er wat gevonden   .xla voorbeelden online, maar ik wilde echt een gemakkelijker te gebruiken, meer   uitgebreide en draagbare bibliotheek. Ik wilde ook een paar basis opnemen   patronen zodat het niet nodig was om het wiel elke keer opnieuw uit te vinden.

Dus ik heb een eenvoudige Excel Add-In Regular Expressions.xla gemaakt die toevoegt   verschillende aangepaste functies om de standaard VBScript-standaard te implementeren   uitdrukkingen.

Hier is de website

Ik heb het met succes gebruikt om bruikbare tekst met behulp van regex te extraheren.

Hier is de code in de addin

' Regular Expressions.xla
'
' ? 2010 Malcolm Poindexter
' This is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License
' as published by the Free Software Foundation, version 3.
' This software is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY;
' without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
' See the GNU General Public License for more details. http://www.gnu.org/licenses/gpl.html
'
' I would appreciate if you would notify me of any modifications or re-distributions of this code at contact@malcolmp.com
' and appropriately attribute me as the original author in the header.
' ------------------------------------------------------------------------------------------------------------------------
'
' This file provides an Excel Add-In containing regular expression processing functions and several pre-defined regular expressions.
' The regular expressions provided are not necessarially exhaustive, but are intended to cover the most common cases.
'
' Regular Expressions Syntax: http://msdn.microsoft.com/en-us/library/1400241x%28VS.85%29.aspx

' -----------------------------
' NAME: xREPLACE
' DESCRIPTION: Replace all portions of the search text matching the pattern with the replacement text.
' -----------------------------
Function xREPLACE(pattern As String, searchText As String, replacementText As String, Optional ignoreCase As Boolean = True) As String
    On Error Resume Next
    Dim RegEx As New RegExp
    RegEx.Global = True
    RegEx.MultiLine = True
    RegEx.pattern = pattern
    RegEx.ignoreCase = ignoreCase
    xREPLACE = RegEx.Replace(searchText, replacementText)
End Function

' -----------------------------
' NAME: xMATCHES
' DESCRIPTION: Find and return the number of matches to a pattern in the search text.
' -----------------------------
Function xMATCHES(pattern As String, searchText As String, Optional ignoreCase As Boolean = True) As String
    On Error Resume Next
    Dim RegEx As New RegExp
    RegEx.Global = True
    RegEx.MultiLine = True
    RegEx.pattern = pattern
    RegEx.ignoreCase = ignoreCase
    Dim matches As MatchCollection
    Set matches = RegEx.Execute(searchText)
    xMATCHES = matches.Count
End Function

' -----------------------------
' NAME: xMATCH
' DESCRIPTION: Find and return an instance of a match to the pattern in the search text. MatchIndex may be used in the case of multiple matches.
' -----------------------------
Function xMATCH(pattern As String, searchText As String, Optional matchIndex As Integer = 1, _
                Optional ignoreCase As Boolean = True) As String
    On Error Resume Next
    Dim RegEx As New RegExp
    RegEx.Global = True
    RegEx.MultiLine = True
    RegEx.pattern = pattern
    RegEx.ignoreCase = ignoreCase
    Dim matches As MatchCollection
    Set matches = RegEx.Execute(searchText)
    Dim i As Integer
    i = 1
    For Each Match In matches
        If i = matchIndex Then
            xMATCH = Match.Value
        End If
        i = i + 1
    Next
End Function

' -----------------------------
' NAME: xMATCHALL
' DESCRIPTION: Find and return a comma-separated list of all matches to the pattern in the search text.
' -----------------------------
Function xMATCHALL(pattern As String, searchText As String, Optional ignoreCase As Boolean = True) As String
    On Error Resume Next
    Dim RegEx As New RegExp
    RegEx.Global = True
    RegEx.MultiLine = True
    RegEx.pattern = pattern
    RegEx.ignoreCase = ignoreCase
    Dim matches As MatchCollection
    Set matches = RegEx.Execute(searchText)
    Dim i As Integer
    i = 1
    Dim returnMatches As String
    returnMatches = ""
    For Each Match In matches
        If i = 1 Then
            returnMatches = Match.Value
        Else
            returnMatches = returnMatches + "," + Match.Value
        End If
        i = i + 1
    Next
    xMATCHALL = returnMatches
End Function

' -----------------------------
' NAME: xGROUP
' DESCRIPTION: Find and return a group from within a matched pattern.
' -----------------------------
Function xGROUP(pattern As String, searchText As String, group As Integer, Optional matchIndex As Integer = 1, _
                Optional ignoreCase As Boolean = True) As String
    On Error Resume Next
    If group <> 0 Then
        group = group - 1
    End If
    Dim RegEx As New RegExp
    RegEx.Global = True
    RegEx.MultiLine = True
    RegEx.pattern = pattern
    RegEx.ignoreCase = ignoreCase
    Dim matches As MatchCollection
    Set matches = RegEx.Execute(searchText)
    Dim i As Integer
    i = 1
    For Each Match In matches
        If i = matchIndex Then
            xGROUP = Match.SubMatches(group)
        End If
        i = i + 1
    Next
End Function

' -----------------------------
' NAME: xSTARTSWITH
' DESCRIPTION: Returns true or false if the search text starts with the pattern.
' -----------------------------
Function xSTARTSWITH(pattern As String, searchText As String, Optional ignoreCase As Boolean = True) As String
    On Error Resume Next
    Dim RegEx As New RegExp
    RegEx.Global = True
    RegEx.MultiLine = True
    RegEx.pattern = "^" + pattern
    RegEx.ignoreCase = ignoreCase
    Dim matches As MatchCollection
    Set matches = RegEx.Execute(searchText)
    xSTARTSWITH = matches.Count > 0
End Function

' -----------------------------
' NAME: xENDSWITH
' DESCRIPTION: Returns true or false if the search text ends with the pattern.
' -----------------------------
Function xENDSWITH(pattern As String, searchText As String, Optional ignoreCase As Boolean = True) As String
    On Error Resume Next
    Dim RegEx As New RegExp
    RegEx.Global = True
    RegEx.MultiLine = True
    RegEx.pattern = pattern + "$"
    RegEx.ignoreCase = ignoreCase
    Dim matches As MatchCollection
    Set matches = RegEx.Execute(searchText)
    xENDSWITH = matches.Count > 0
End Function

' ************************************
' Regular Expression Definitions
' ************************************

' -----------------------------
' NAME: xxEMAIL
' DESCRIPTION: Pattern to match an email address.
' -----------------------------
Function xxEMAIL() As String
    xxEMAIL = "\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}\b"
End Function

' -----------------------------
' NAME: xxUSZIP
' DESCRIPTION: Pattern to match an US Zip code.
' -----------------------------
Function xxUSZIP() As String
    xxUSZIP = "\b(?!0{5})(\d{5})(?!-0{4})(-\d{4})?\b"
End Function

' -----------------------------
' NAME: xxPHONE
' DESCRIPTION: Pattern to match a phone number.
' -----------------------------
Function xxPHONE() As String
    xxPHONE = "\b[01]?[- .]?\(?[2-9]\d{2}\)?\s?[- .]?\s?\d{3}\s?[- .]?\s?\d{4}(\s*(x|(ext))[\.]?\s*\d{1,6})?\b"
End Function

' -----------------------------
' NAME: xxURL
' DESCRIPTION: Pattern to match a url.
' -----------------------------
Function xxURL() As String
    xxURL = "\b((ftp)|(https?))\://[a-zA-Z0-9\-\.]+\.[a-zA-Z]{2,3}\b"
End Function


' ************************************
'   Insert Function Dialog Category Setup
' ************************************
Sub AddCategoryDescription()
    Application.MacroOptions Macro:="xREPLACE", _
        Description:="Replace all portions of the search text matching the pattern with the replacement text.", _
        Category:="Regular Expressions"

    Application.MacroOptions Macro:="xMATCHES", _
        Description:="Find and return the number of matches to a pattern in the search text.", _
        Category:="Regular Expressions"

    Application.MacroOptions Macro:="xMATCH", _
        Description:="Find and return an instance of a match to the pattern in the search text. MatchIndex may be used in the case of multiple matches.", _
        Category:="Regular Expressions"

    Application.MacroOptions Macro:="xMATCHALL", _
        Description:="Find and return a comma-separated list of all matches to the pattern in the search text.", _
        Category:="Regular Expressions"

    Application.MacroOptions Macro:="xGROUP", _
        Description:="Find and return a group from within a matched pattern.", _
        Category:="Regular Expressions"

    Application.MacroOptions Macro:="xSTARTSWITH", _
        Description:="Returns true or false if the search text starts with the pattern.", _
        Category:="Regular Expressions"

    Application.MacroOptions Macro:="xENDSWITH", _
        Description:="Returns true or false if the search text ends with the pattern.", _
        Category:="Regular Expressions"

    '**** Regular Expressions ****

    Application.MacroOptions Macro:="xxEMAIL", _
        Description:="Pattern to match an email address.", _
        Category:="Regular Expressions"

    Application.MacroOptions Macro:="xxUSZIP", _
        Description:="Pattern to match an US Zip code.", _
        Category:="Regular Expressions"

    Application.MacroOptions Macro:="xxPHONE", _
        Description:="Pattern to match a phone number.", _
        Category:="Regular Expressions"

    Application.MacroOptions Macro:="xxURL", _
        Description:="Pattern to match a url.", _
        Category:="Regular Expressions"
End Sub

-2
2018-04-27 09:22