Parse address field

[aparna0208]

Hi,

I have a file which includes address column as seen below. I need to parse it into different components as street address, city etc. I am not very familiar with regex and need some help with it. Any inputs on this would be of great help. Thank you in advance!

Input 

Address

8427 Old Bingham Hwy Ste. A  West Jordan Utah UT US

10 Elyise Rd Suite L4  Monsey New York NY US

3525 Del Mar Heights Rd Ste 812  san diego California CA US

Output

Street Address                                                City                                State                    Country

8427 Old Bingham Hwy Ste. A                          West Jordan                   UT                         US

10 Elyise Rd Suite L4                                        Monsey                           NY                        US

3525 Del Mar Heights Rd Ste 812                     san diego                        CA                        US

[DataNath]

I've attached a solution that works for your sample. However, I'm not aware of any way to bring a list into a capture group so I've had to include the (X|Y|Z) capture group manually. There may be a dynamic way to do this so you don't have to include all US cities in there but that's beyond me, apologies! Workbook attached.

[aparna0208]

@DataNath Thank you for your inputs. This helps to some extent. But yes, as you said trying to see if there is a dynamic or some other way to parse out street address without having to do it manually

[PhilipMannering]

Hi @aparna0208 

You could try using the Google Maps Platform api: https://developers.google.com/maps/documentation/geocoding/requests-geocoding

You'll need to get your own key. But you can make a certain number of requests free and I don't think you'll get better accuracy than Google maps. See workflow attached.

[aparna0208]

@PhilipMannering wow this looks awesome and I learnt something new:) I tried running your workflow but it errored out. As you said probably I need to get a key. Not sure if this includes cost and any other complex thing so trying to figure out from the link you provided.