Select to view content in your preferred language

Extracting an Address from a string

195
3
05-07-2024 06:18 AM
Labels (1)
SLouq
by MVP Regular Contributor
MVP Regular Contributor

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.

0 Kudos
3 Replies
clt_cabq
Occasional Contributor III

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.

0 Kudos
BobBooth1
Esri Contributor

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
MVP Esteemed Contributor

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.

0 Kudos