CODEWITHSUNDEEP
stringvbaexcel
bathtubandatoaster
bathtubandatoaster

Reputation: 67

Using Excel for address, string manipulation

I'm currently doing some work with a very large data source on city addresses where the data looks something like this.

source:

NO. 174 5/F 174 SMITH STREET 174 SMITH STREET TOR

i've used a function which removes the duplicates shown on extendoffice. https://www.extendoffice.com/documents/excel/2133-excel-remove-duplicate-characters-in-string.html

Function RemoveDupes2(txt As String, Optional delim As String = " ") As String
Dim x

With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For Each x In Split(txt, delim)
        If Trim(x) <> "" And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
    Next
    If .Count > 0 Then RemoveDupes2 = Join(.keys, delim)
End With

End Function

the second column has become this:

NO. 174 5/F SMITH STREET TOR

what I want to do now is to turn it into this (removing the NO. and moving the street number):

5/F 174 SMITH STREET TOR

Would the the best way to accomplish this? Is there a way just to move the street number beside the street name.

An idea I had was to break down the address into different strings and putting them into different columns and rearranging them.

Also I was hoping I could write something in VBA, since the addresses are all different lengths and have various spacing

Thanks.

Upvotes: 2

Views: 144

Answers (1)

user3598756
user3598756

Reputation: 29421

you may try this:

Function RemoveDupes2(txt As String, Optional delim As String = " ") As String
    Dim x As Variant, arr As Variant, temp As Variant

    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextCompare
        For Each x In Split(txt, delim)
            If Trim(x) <> "" And Not .exists(Trim(x)) Then .Add Trim(x), Nothing
        Next
        If .count > 0 Then
            arr = Split(Replace(Join(.keys, delim), "NO." & delim, ""), delim)
            temp = arr(0)
            arr(0) = arr(1)
            arr(1) = temp
            RemoveDupes2 = Join(arr, delim)
        End If
    End With
End Function

Upvotes: 2

Related Questions