How to add ordinals to an address string using Excel VBA

Question

I am trying to cleaning up some addresses. I already have some code to change "Sw","Ne" and capitalizing them.

However, I would like to add one more step to tack on ordinals to rural addresses in the same code if possible.

all the addresses I need to updated start with 5 numbers then space then 3 numbers. "NNNNN NNN"

I have some code to add ordinals, I just need a way to only add it to those 3 numbers after the 5. Example "56579 123" to "56579 123rd" and leave the rest of the address alone.

Here is my current code.

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

Here is the ordinal function but this only works if i have a single number in a cell.

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

This is basically what I am looking to achieve.

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

I don't know how ordinals work in english but... you can use `Case Is = 1` `Case Is = 2` `Case Is > 2` or whatever you need. — Damian, May 30 '19 at 15:11
If the above commented is handled for you(you can verify the street number) you might want to read [this](https://stackoverflow.com/a/26627043/7558682) because now that I saw your code seems that you need to use `Like` operator. If you need to first fetch the street number I'm going with SJR and cyber on the RE. — Damian, May 30 '19 at 15:16
That is the correct format for the address. 213th N Apple St is wrong. — SkysLastChance, May 30 '19 at 15:16

score 2 · Answer 1 · answered May 30 '19 at 15:44

you could have TidyUp function process the second "numeric" substring (if any)

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

score 2 · Accepted Answer · answered May 30 '19 at 15:49

2

You can join the two:

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"
        If IsNumeric(arr(i)) And i > 0 Then
            Dim temp As String
            Select Case CLng(Right(arr(i), 1))
                Case 1
                temp = arr(i) & "st"
                Case 2
                temp = arr(i) & "nd"
                Case 3
                temp = arr(i) & "rd"
                Case Else
                temp = arr(i) & "th"
            End Select
            Select Case CLng(Right(arr(i), 2))
                Case 11, 12, 13
                temp = arr(i) & "th"
            End Select
            arr(i) = temp
        End If

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

answered May 30 '19 at 15:49

Scott Craner

148,073
10
49
81

quite as what I proposed, actually… BTW `If IsNumeric(arr(i)) And i > 0 Then` would process any "numeric" string after the first one, i.e. not only the 2nd. Although this should not be a real issue, given string "layout" – DisplayName May 30 '19 at 16:00
@DisplayName I did it in case there ever was `145 W 145th st` Most the cities I lived in have a ordinal direction in front of the street name. even with numbers. I was thinking about future readers. – Scott Craner May 30 '19 at 16:02
@ScottCraner This exactly what I had in mind. I am having a problem with a few though I have a few problems "PO Box 112" is getting changed to PO Box 112th and "1234 11 Ave NW Lot 22" is getting changed to "1234 11th Ave NW Lot 22nd" I would not want the PO BOX's and lot numbers to get a ordinals. Things like apt numbers are getting caught as well. – SkysLastChance May 30 '19 at 18:42
If your pattern of it only being the second "word" is true, change the `i > 0` to `i = 1` @SkysLastChance – Scott Craner May 30 '19 at 18:45

How to add ordinals to an address string using Excel VBA

2 Answers2