Parse Address/City/State Data Into Multiple Cells

jo15765

Well-known Member
Joined
Sep 23, 2011
Messages
570
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)
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Sep 23, 2011
Messages
570
@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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
May 31, 2013
Messages
89
As stated in other post data may vary but you can give this a try



Code:
[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

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
@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.
Code:
'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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,489
Messages
5,601,986
Members
414,489
Latest member
Xlambda

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top