excel - 使用 isNumeric 从地址中提取邮政编码

Question

我想从地址中提取邮政编码。我尝试isNumeric了以下方法从地址中提取 6 个数字。有些地址有 5 位数，有些有 6 位数邮政编码。但是有一些错误，有时11900只显示1900，08000显示8000，4位数字也显示。

Range("A2").Select
i = 2
Do While ActiveCell <> ""
    Address = UCase(Trim(Range("C" & CStr(i))) + " " + Trim(Range("D" & CStr(i))) + " " + Trim(Range("E" & CStr(i))) + " " + Trim(Range("F" & CStr(i))))

    For p = 1 To Len(Address)
      If IsNumeric(Mid(Address , p, 6)) Then
         Range("O" & CStr(i)) = Mid(Address, p, 6)
      End If
    Next p

    ActiveCell.Offset(1, 0).Select
    i = i + 1
Loop

excel输出

Address                                                               Postal Code
Wisma Pansar, 23-27 Jln Bengkel P.O. Box 319, 96007 Sibu Sarawak        96007
Wisma Lim , Lot 50A, Sec. 92A, 3.1/2 Sg Besi, 57100 Kuala Lumpur        57100
No. 265A, Jalan Sungai Petani 08300 Gurun Kedah Darul Aman              8300
No. 39, Jalan Nipah, Taman Lip Sin 11900  Sungai Nibong Pulau Pinang    1900
4-G, Lebuh Sungai Pinang 1 Sri Pinang 11600 Jelutong Pulau Pinang       11600
539/2, Gypsum Metropolitan Tower, Rajthevee Bangkok 10400, Thailand     0400,
LOTS 1869 &1938, 18th MILE KAJANG, SEMENYIH ROAD SELANGOR D.E.          1938, *no postal code in address
36a, Joo Chiat Place, Singapore 427760                                  0

标签： excelvba

Answer 1

我的意思是这样的：

Sub test()
    Dim c As Range, p As Long, v, addr, i As Long, hit As Boolean

    Set c = Range("A2") 'no need to select the cell
    Do While c <> ""
        addr = c.Value 'using your examples
        hit = False
        For p = 1 To Len(addr)
            'will accept 5 or 6 digits - prefer 6
            ' so count down...
            For i = 6 To 5 Step -1
                v = Mid(addr, p, i)
                If v Like String(i, "#") Then
                    c.Offset(0, 1).NumberFormat = "@" 'in case of leading zero
                    c.Offset(0, 1).Value = v
                    hit = True
                    Exit For
                End If
            Next i
            If hit Then Exit For
        Next p
        Set c = c.Offset(1, 0)
    Loop
End Sub

正则表达式方法可能会更好。