topic Re: Extracting an Address from a string in Data Management Questions

Extracting an Address from a string

SLouq — Tue, 07 May 2024 13:18:08 GMT

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.

Re: Extracting an Address from a string

clt_cabq — Tue, 07 May 2024 13:35:19 GMT

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.

Re: Extracting an Address from a string

BobBooth1 — Tue, 07 May 2024 14:12:40 GMT

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.

Re: Extracting an Address from a string

JoshuaBixby — Tue, 07 May 2024 14:55:17 GMT

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.