Help with splitting text from one cell into multiple

[ochow01]

Hello everyone I'm hoping someone can point me in the right direction here. I'm not the most experienced person with excel, but usually I can goggle my road blocks and find a way around them. Unfortunately I'm coming up short this time as I can't seem to find a way to do what I need. I'm hoping someone here has a solution. I've done a search on how to split text into separate columns but there is always some sort of delimiter that makes it possible, in my case I can seem to find one. So I have a large set of data containing addresses in one column. What I want to do is separate the street address, city, state, and zip into separate columns. I've been able to get the zip by using the right function, and the state is easy because they're all in CA. The street and city is where I'm having issues. In some cells the data is something like this, 111 Somewhere St. Excel CA, 90000, in others I have 12345 Here Ave Amazing City, CA 11234.


https://drive.google.com/file/d/0B995serHQILATkVuTm1RYlJHeEk/view?usp=sharing <---- Link to my excel sheet. This is just a portion of the data, the actual file is several thousand rows

Thank you in advance for any help! I hope everyone has a great week!

 

[Deutz]

You could convert text to columns and specify Space as the delimiter. Then just concatenate the columns that make up the address/state etc. the only difficulty could be if there are varying number of spaces in the address etc in which case you would have to do a visual inspection of the results to ensure it was concatenated correctly. Another option may be to request the person who pulled the data to provide it split up correctly into separate columns so you can avoid the headache altogether.

Regards
Deutz

 

[NotoriousPopol]

Sometimes the street has 2 words, sometimes one, and same for the city. If you don't mark the separation, I don't really know how you could split them...

 

[jolivanes]

Is this a possible start?
Addresses in Column A starting at the 2nd row.
Columns B, C and D are free to use.
Need to fix the "CA" in some yet but it is well past my bedtime.

Sub Maybe()
    Dim addressArr, i As Long, c As Range
    addressArr = Array("St.", "St", "Street", "Ave", "Avenue")
    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        For i = LBound(addressArr) To UBound(addressArr)
            If InStr(c.Value, addressArr(i)) > 0 Then
                c.Offset(, 1).Value = Left(c, Len(Split(c.Value, addressArr(i))(0)) + 1 + Len(addressArr(i)))
                c.Offset(, 2).Value = Split(Trim(Mid(c, Len(c.Offset(, 1)) + 1, 99)), ",")(0)
                c.Offset(, 3).Value = Trim(Split(Trim(Mid(c, Len(c.Offset(, 1)) + 1, 99)), ",")(1))
            End If
        Next i
    Next c
End Sub