Parse Address/City/State Data Into Multiple Cells

[jo15765]

I have data that is set-up like this and i am needing to split it into individual columns
Hallway Oats & Freeform 2212 Blue Ridge Lane Redfren, SC 99121
Name = Hallway Oats & Freeform
Address = 2212 Blue Ridge Lane
City = Redfren
State = SC
Zip = 99121

What syntax would split this data into individual cells? (Note, not all the Names have a & in them some of them do not so that is not a good way to split the data)

 

[Joe4]

Without delimiters between each piece of data, in order to do this, you would need to clearly define the rules for us, i.e.
Split Name and Address based on some condition/scenario (and explain exactly what that condition/scenario is), etc.

I have seen this attempted many times, and the issue is how much the data can vary, it rarely follows any set rules you can use.
You can easily split state and zip code, but Name, Address, and City are going to be tricky, since they could change so much.
For example, city may have one word or two (i.e. New York or Carson City)
Address may have multiple numbers in them, or none at all (i.e. One Bank Plaza or 111 Main Street Apartment 14)
Names could really have any number of words and/or numbers

Without have any set defined rules, it is impossible to come up with a solution that will always work (I always tell people Excel isn't magic, it can only do what you tell it to, so if you cannot describe clearly defined rules that always work, it is impossible come up with a formula that will always work).

Usually, in these cases, the best you can do is to come up with a formula that should work MOST of the time, but it will still require people manually reviewing it to catch/fix all those cases that don't follow the rules.

Or, go back to the data source and demand that they provide the data in a better format!

 

[jo15765]

@joe4 - that is what I was thinking, but thought I would ask the Excel Ninja's before I completely ruled out all options.

Thank you for the reply.

 

[Joe4]

Yes, unfortunately, Excel (and computers in general) cannot really think for themselves (yet!).
They can only do what we tell them. So if we cannot define rules that tell it exactly how to approach each one to determine where all the splits should be, we cannot program it.

(Then again, maybe not so unfortunate! If computers could think for themselves, human kind might be in trouble!!! And then we would have to send Arnold Schwarzenegger back in time to save the human race!).

 

[keiserj]

As stated in other post data may vary but you can give this a try

[B]Name:[/B] =LEFT(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")-2))
[B]Address:[/B] =LEFT(MID(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),FIND(",",B2,1)-(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")))),LEN(MID(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),FIND(",",B2,1)-(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")))))-LEN(E2))

[B]City:[/B] =TRIM(RIGHT(SUBSTITUTE(MID(B2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789")),FIND(",",B2,1)-(MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B2&"0123456789"))))," ",REPT(" ",255)),255))

[B]State:[/B] =MID(RIGHT(B2,8),1,2)
[B]Zip:[/B] =RIGHT(B2,5)

 

[SpillerBD]

@joe4 - that is what I was thinking, but thought I would ask the Excel Ninja's before I completely ruled out all options.

Thank you for the reply.

You might be able to bounce the address of one of the mapping services to get back a standardized version of the address that could be parsed.
That would be a modification of a macro that retrieves the GPS coordinates.
The following should return a better formatted text string for validating and parsing.

'Geocoded Address
Public Function GetGeoCodeAddress(Address As String) As String ', Longitude As Double, Latitude As Double, Status As String) As Boolean
' Declare variables and set return value to false by default
GetGeoCodeAddress = False
Dim response As DOMDocument60
Dim http As XMLHTTP60
Dim node As IXMLDOMNode
Dim nodes As IXMLDOMNodeList
Set http = New XMLHTTP60
' Read the data from the website
On Error Resume Next
' Open an XML request from Google using their GeoCode API
http.Open "GET", "[URL]https://maps.googleapis.com/maps/api/geocode/xml?address[/URL]=" & Replace(Address, " ", "%20"), False
http.send
Set response = http.responseXML
' get the status node. This node tells you whether your search succeeded - OK means success. Any other status means some kind of error or address not found.
Set node = response.SelectSingleNode("/GeocodeResponse/status")
If node.nodeTypedValue <> "OK" Then
Status = node.nodeTypeString
Else
Set nodes = response.SelectNodes("/GeocodeResponse/result")
' check for multiple addresses if we found more than 1 result then error out.
If nodes.Length > 1 Then
MsgBox ("Found Multiple Matches for Address: " & Address)
Else
' grab the latitude and longitude from the XML response
Set node = response.SelectSingleNode("/GeocodeResponse/result/formatted_address")
GetGeoCodeAddress = node.nodeTypedValue
End If
End If
 Set http = Nothing
 Set response = Nothing
 End Function