时间戳

首页 > 解决方案 > 如何排除字符串开头和结尾的任何单个字母，但如果没有任何内容，仍然会得到匹配

regex - 如何排除字符串开头和结尾的任何单个字母，但如果没有任何内容，仍然会得到匹配

问题描述

我正在尝试将地址从 1 个单元格与其他单元格分开。我从包含所有比利时城市的大数组中匹配城市。目前它工作正常，除了这种例外：

在比利时，我们有一个城市叫'On'。这也是您可以在街道名称中找到的词。由于我不知道用户会进入城市后面的街道还是街道后面的城市，所以我必须将一个与另一个区分开来（我也不能确定是否会使用大写字母）。

区别在于，街道上的“on”几乎肯定会在开头或结尾处粘贴一个字母。例子：

'Onderstraat'、'Donderstraat'、'Avenue du Bon'

我试过这个：

objRegExp.Pattern = "[^a-zA-ZàáâäÀÁÂÄèéêëÈÉÊËôöÔÖùúûüÙÚÛÜïÏçÇ]" & LCase(vaCitiesA(i)) & "[^a-zA-ZàáâäÀÁÂÄèéêëÈÉÊËôöÔÖùúûüÙÚÛÜïÏçÇ]"

但是当他们把城市放在最后时，它就不匹配了，因为城市后面没有任何内容（下例中的 Schaarbeek）例如：'blv Albert II 121a boîte 1, 1030 Schaarbeek'

我想要的是在上面，它会找到 Schaarbeek，但是对于 Onderstraat、Donderstraat 或 av du Bon，它不应该与城市 'On' 匹配

标签： regexexcelvba

解决方案

OP的工作代码从问题中删除并以答案形式发布：

Dim vaCitiesA As Variant
Dim xSelection As Range
Dim xCity As String
Dim rest As String

vaCitiesA = Array("Hody", "Hour", "Houx", "HOVE", "Hyon", "Impe", "Izel", "Jeuk", "Kain", "Laar", "Lauw", "LEDE", "Leke", "LENS", "Leut", "LIER", "LINT", "Mark", "Mazy", _
    "Mean", "Meer", "Mere", "Meux", "Moen", "Moha", "MONS", "Mont", "Mont", "Muno", "NATO", "NIEL", "Nimy", "OHEY", "Oizy", "OLEN", "OLNE", "Omal", "Onoz", _
    "Orcq", "Oret", "Paal", "PECQ", "PEER", "Perk", "Redu", "Reet", "Roly", "Roux", "RTBF", "Seny", "Soye", "Suxy", "Thon", "Thys", "Velm", "VISE", "Vivy", _
    "Waha", "Ways", "Werm", "ZELE", "ANS", "ATH", "Aye", "Bra", "Eke", "Ere", "Goe", "HAM", "HUY", "Lot", "Mal", "MOL", "Ogy", "Pry", "Roy", _
    "Scy", "SOC", "Soy", "SPA", "Spy", "VRT", "VTM", "AS", "Lo", "My", "On")

Set xSelection = Application.Selection
For Each Rng In xSelection

    Dim allMatches As Object
    Dim objRegExp As Object
    'Initializing an Instance
    Set objRegExp = CreateObject("VBScript.RegExp")
    'Setting the Properties
    With objRegExp
        .Global = True
        .IgnoreCase = True
    End With

    xCity = ""
    rest = Rng.Value


    'a
    rest = Replace(rest, "à", "a")
    rest = Replace(rest, "â", "a")
    rest = Replace(rest, "á", "a")
    rest = Replace(rest, "À", "A")
    rest = Replace(rest, "Â", "A")
    rest = Replace(rest, "Á", "A")
    'e
    rest = Replace(rest, "ë", "e")
    rest = Replace(rest, "é", "e")
    rest = Replace(rest, "è", "e")
    rest = Replace(rest, "ê", "e")
    rest = Replace(rest, "Ë", "E")
    rest = Replace(rest, "É", "E")
    rest = Replace(rest, "È", "E")
    rest = Replace(rest, "Ê", "E")
    'o
    rest = Replace(rest, "ö", "o")
    rest = Replace(rest, "ô", "o")
    rest = Replace(rest, "Ö", "O")
    rest = Replace(rest, "Ô", "O")
    'u
    rest = Replace(rest, "ü", "u")
    rest = Replace(rest, "û", "u")
    rest = Replace(rest, "ù", "u")
    rest = Replace(rest, "ú", "u")
    rest = Replace(rest, "Ü", "U")
    rest = Replace(rest, "Û", "U")
    rest = Replace(rest, "Ù", "U")
    rest = Replace(rest, "Ú", "U")
    'i
    rest = Replace(rest, "ï", "i")
    rest = Replace(rest, "Ï", "I")
    'c
    rest = Replace(rest, "ç", "c")
    rest = Replace(rest, "Ç", "C")
    'special
    rest = Replace(rest, "'", "")
    rest = Replace(rest, "-", " ")

    Text = LCase(rest)

    For i = LBound(vaCitiesA) To UBound(vaCitiesA)
        '''''''''''''''''''''''''''''''''''''
    objRegExp.Pattern = "(^|[^a-zA-ZàáâäÀÁÂÄèéêëÈÉÊËôöÔÖùúûüÙÚÛÜïÏçÇ])" & LCase(vaCitiesG(i)) & "(?![a-zA-ZàáâäÀÁÂÄèéêëÈÉÊËôöÔÖùúûüÙÚÛÜïÏçÇ])"
    Set allMatches = objRegExp.Execute(Text)
    If objRegExp.Test(Text) Then
        xCity = vaCitiesA(i)
        Exit For
    End If
    ''''''''''''''''''''''''''''''''''''''''
Next i
If xCity <> "" Then
    'for the moment, I choose last match, but could be problematic if there is a short city like 'ON' in front of the street
        x = allMatches.Count - 1
        lCityPos = allMatches(x).FirstIndex
        rest = Replace(rest, Mid(rest, lCityPos + 1, Len(xCity) + 1), "", , 1)
    End If

推荐阅读