时间戳

首页 > 解决方案 > 如何使用 Excel VBA 将序数添加到地址字符串

excel - 如何使用 Excel VBA 将序数添加到地址字符串

问题描述

我正在尝试清理一些地址。我已经有一些代码可以更改“Sw”、“Ne”并将它们大写。

但是，如果可能的话，我想再增加一个步骤，以在同一代码中将序数附加到农村地址。

我需要更新的所有地址都以 5 个数字开头，然后是空格，然后是 3 个数字。“NNNNNNN”

我有一些添加序数的代码，我只需要一种方法将其仅添加到 5 之后的这 3 个数字中。例如“56579 123”到“56579 123rd”，并保留其余的地址。

这是我当前的代码。

Public Function TidyUp(sin As String) As String
    Dim i As Long
    arr = Split(sin, " ")
    For i = LBound(arr) To UBound(arr)

        If UCase(arr(i)) = "NE" Then arr(i) = "NE"
        If UCase(arr(i)) = "NW" Then arr(i) = "NW"
        If UCase(arr(i)) = "SE" Then arr(i) = "SE"
        If UCase(arr(i)) = "SW" Then arr(i) = "SW"

    Next i
    TidyUp = Join(arr, " ")
End Function

这是序数函数，但这仅在我在单元格中有一个数字时才有效。

Function AddOrdinal(Address As String) As String

Select Case CLng(VBA.Right(Address, 1))
    Case 1
    AddOrdinal = Address & "st"
    Case 2
    AddOrdinal = Address & "nd"
    Case 3
    AddOrdinal = Address & "rd"
    Case Else
    AddOrdinal = Address & "th"
End Select
Select Case VBA.CLng(VBA.Right(Address, 2))
    Case 11, 12, 13
    AddOrdinal = Address & "th"
End Select
End Function

这基本上就是我想要实现的目标。

Address List        Expected Output
12345 673 Ave       12345 673rd Ave
213 N Apple St      213 N Apple St
69818 221st Rd      69818 221st Rd
569 Sw Maple Dr     569 SW Maple Dr
10005 654 Dr        10005 654th Dr
369 Ne Banana St    369 NE Banana St
54489 412th St      54489 412th St
986 W Timber St     986 W Timber St
79532 771 Dr        79532 771st Dr
126 E Washington Ave126 E Washington Ave
56898 422 Dr        56898 422nd Dr

标签： excelvba

解决方案

您可以让 TidyUp 函数处理第二个“数字”子字符串（如果有）

Public Function TidyUp(sin As String) As String
    Dim i As Long, arr
    arr = Split(sin, " ")
    For i = LBound(arr) To UBound(arr)

        Select Case UCase(arr(i))
            Case "NE", "NW", "SE", "SW"
                arr(i) = UCase(arr(i))
        End Select

        If i = 1 Then ' check 2nd substring
            If IsNumeric(arr(i)) Then arr(i) = AddOrdinal(CStr(arr(i))) 'if it's a "numeric" substring then have it processed by AddOrdinal() function
        End If

    Next

    TidyUp = Join(arr, " ")
End Function

推荐阅读