Extracting an Address from a string

[SLouq]

I have an excel spreadsheet which has names and addresses in it. They are listed as 

first name last name 12344 Street name Street Type

I am trying to break this down and put each in it's own column in Excel so I can join it with my attribute table of a hosted feature layer in ArcGIS Pro.

Can anyone help me with the Excel function which would accomplish this?

Note: I figured out how to get the name from the Excel cells but having trouble with the address number which is the middle of the string.

[clt_cabq]

How consistent are your data? if its always "john smith 1234 Main St" you could use python to split your addresses at the spaces so you end up a list that would look like ["john", "smith", 1234, "Main", "St"] and from there write elements right of "smith" into a field that is properly formatted. The problem with these kind of data is if there are weird cases that don't fit this pattern.

[BobBooth1]

Try copying the column with the long string into a new sheet and use Excel's Text to Columns to split it on spaces. You will still probably have to do some manual cleanup.

[JoshuaBixby]

If money is no object, there is always Entity Extraction Software | Unstructured Data Analysis | ArcGIS LocateXT (esri.com), but I think your situation is simple enough Excel's Text to Columns should work.