Splitting address, city, state, and zip, lacking d

Question

I have an excel spreadsheet that contains entire addresses packed in a single cell without delimiters. The addresses look like this:

2701 NW 64TH TER MARGATE FL 33063-1703 
901 NE 8 ST HALLANDALE BEACH FL 33009-2626 
1840 DEWEY ST UNIT 305 HOLLYWOOD FL 33020 
3049 NE 4 AVE WILTON MANORS FL 33334-2047
650 NE 56 CT OAKLAND PARK FL 33334-3528 

So the first five cells in column A would contain the above addresses.

As you can see, some of the cities consist of two words but the state is always FL or NY. All I need to do is separate the address, city, state, and zip in their own columns. I'm hoping there's a way to do this in VBD (Visual Basic for Developers) in excel. So I can put it into a macro.

I have an idea of how it can be done, but my VBD is limited:

stateArray = Split("FL, NY")
cityArray = Split("Fort Lauderdale","Sunrise","Oakland Park")

For example, another programming language you might do something like this:

var arrStates, arrCities
arrCities = ["Fort Lauderdale", "Sunrise", "Oakland Park"]
arrStates = ["FL", "NY"]

var findAddress = function(curCity, curState){
    for(var i=0; i < arrCities.length; i < arrStates.length; i--){

        (arrCities[i] == curCity) ? arrCities[i] = CurCity : arrCities[i] = null;
        (arrStates[i] == curState) ? arrStates[i] = curState : arrStates[i] = null;

    }   

    if(arrCities[i] >= 0){
        var city = arrCities[i];
    }

    if(arrStates[i] >= 0){
        var state = arrStates[i];
    }

    createTable(city, state);

}

var createTable = function(city, state){
    var tbl = document.createElement("Table");
    var newRow = document.createElement("tr");
    tbl.appendChild(newRow);
    cols = [city, state];

    for(var i=0; i < cols.length; i++){
        var newCol = document.createElement("td");
        newCol.innerText = cols[i];
        newRow.appendChild(newCol);
    }
}

Thanks for any response.

Answer 1

It seems that if you have to type out all the cities, you might as well just split all the cells manually. It may be easier to identify all the street types and use that as a delimiter. Note the spaces around the strings in the array.

Sub SplitAddresses()

    Dim vaStates As Variant
    Dim vaStreets As Variant
    Dim i As Long
    Dim rCell As Range
    Dim sAddress As String
    Dim sCity As String, sState As String
    Dim sZip As String
    Dim lStreetPos As Long, lStatePos As Long

    vaStates = Array(" FL ", " NY ")
    vaStreets = Array(" TER ", " ST ", " AVE ", " CT ")

    For Each rCell In Sheet1.Range("A1:A5").Cells
        sAddress = "": sCity = "": sZip = "": sState = ""
        For i = LBound(vaStreets) To UBound(vaStreets)
            lStreetPos = InStr(1, rCell.Value, vaStreets(i))
            If lStreetPos > 0 Then
                sAddress = Trim(Left$(rCell.Value, lStreetPos + Len(vaStreets(i)) - 1))
                Exit For
            End If
        Next i

        For i = LBound(vaStates) To UBound(vaStates)
            lStatePos = InStr(1, rCell.Value, vaStates(i))
            If lStatePos > 0 Then
                sCity = Trim(Mid$(rCell.Value, Len(sAddress) + 1, lStatePos - Len(sAddress) - 1))
                sState = Trim(Mid$(rCell.Value, lStatePos + 1, Len(vaStates(i)) - 1))
                sZip = Trim(Mid$(rCell.Value, lStatePos + Len(vaStates(i)), Len(rCell.Value)))
                Exit For
            End If
        Next i

        rCell.Offset(0, 1).Value = "'" & sAddress
        rCell.Offset(0, 2).Value = "'" & sCity
        rCell.Offset(0, 3).Value = "'" & sState
        rCell.Offset(0, 4).Value = "'" & sZip

    Next rCell

End Sub

Answer 2

Here is some VBA code to get you started: you would need to add error handling

Option Explicit
Option Compare Text
Sub SplitAddress()
    Dim vStates As Variant
    Dim vCities As Variant
    Dim vInput As Variant
    Dim vAddress() As Variant
    Dim j As Long
    Dim str1 As String

    ' States/Cities/Inputs are named ranges containing the data
    vStates = [States]
    vCities = [Cities]
    vInput = [Inputs]

    ReDim vAddress(1 To UBound(vInput) - LBound(vInput) + 1, 1 To 4)
    For j = 1 To UBound(vInput)
        str1 = Trim(CStr(vInput(j, 1)))
        If Len(str1) = 0 Then Exit For
        FindSplit j, 3, str1, vStates, vAddress()
        FindSplit j, 2, str1, vCities, vAddress()
    Next j

    ActiveSheet.Range("A2").Resize(UBound(vAddress), UBound(vAddress, 2)) = vAddress
End Sub
Sub FindSplit(j As Long, k As Long, str1 As String, vItems As Variant, vAddress() As Variant)
    Dim iPos As Long
    Dim jItem As Long
    Dim strItem As String

    For jItem = 1 To UBound(vItems)
        strItem = Trim(CStr(vItems(jItem, 1)))
        iPos = InStr(str1, " " & strItem & " ")
        If iPos > 0 Then
            vAddress(j, k) = Mid(str1, iPos + 1, Len(strItem))
            If k = 3 Then
                vAddress(j, k + 1) = Right(str1, Len(str1) - (iPos + 3))
                str1 = Left(str1, iPos)
            Else
                vAddress(j, 1) = Left(str1, iPos - 1)
            End If
            Exit For
        End If
    Next jItem
End Sub

Answer 3

In situations like this I try to forget that I'm programming, and just think about what sort of thought processes I would use to figure out what each was normally.

2701 NW 64TH TER MARGATE FL 33063-1703 
901 NE 8 ST HALLANDALE BEACH FL 33009-2626 
1840 DEWEY ST UNIT 305 HOLLYWOOD FL 33020 
3049 NE 4 AVE WILTON MANORS FL 33334-2047
650 NE 56 CT OAKLAND PARK FL 33334-3528 

At 1st things may seem hectic, but if you look closer there are patterns.

1. The addresses all start with a set of #s as the start of the street address
2. The street address always ends in a street type abbreviation
3. If there is a unit of the building it is after the street address
4. The addresses all end with the zip code
5. Before the zip code is the state abbreviation
6. The City name is smack dab in the middle for the taking if everything else is stripped away.

This makes the pattern as follows

Street # : Street Type : Unit {Optional} : City : State: Zip Code

Strip each piece off of a temporary string variable using string functions and you should be able to rebuild it fairly easily.

Hope that helps.