Parsing addresses from US and foreign countries into new columns

[nberumen]

Hello All! 😃

I need some help with my workflow. I am trying to parse these addresses into different columns as uniform and clean as possible. I do have many different countries in my data. I was thinking maybe a Regex Tool with a certain formula but cannot seem to figure it out. This the certain column from a worksheet that I am working on.

Here is an example of what my data looks like below:

First row is the company name

Second is the street name

Third row is the city/province and zip code

Fourth row is the country

Billing Address

Laundromat 8576 Viviane Squares Suite 460 New Town Alaska 91221 United States

Grocery Store PO Box 1234  Land Hawaii 27145-1111 United States

Business Corporation   29598 Gabe Mall    Suite 921  Upton New York 92821  United States

Market Store United States

ABC Industry  18 Marshall Ave, Suite 300 Collegeville PA 19426  United States

123 Store  1560 Yonge St #107 Ontario QC M4V 3C6  Canada

Toy Mart Room 16z, Li Xiang 0769c2dong Bran Yuan 602hao Fang Floris - Wiig District, Dongguan, Wong Province 123456 China

This is the result that I am trying to achieve

Billing Name	Billing Address	Suite Number	Billing City / District	Billing State / Province	Billing Country	Billing Zip
Laundromat	8576 Viviane Squares	Suite 460	New Town	AK	United States	91221
Grocery Store	PO Box 1234		Land	HI	United States	27145-1111
Business Corporation	29598 Gabe Mall		Upton	NY	United States	92821
Market Store					United States
ABC Industry	18 Marshall Ave	Suite 300	Collegeville	PA	United States	19426
123 Store	1560 Yonge	St #107	Ontario	QC	Canada	M4V 3C6
Toy Mart	Li Xiang 0769c2dong Bran Yuan 602hao Fang	Room 16z	Dongguan	Wong Province	China	123456

[Felipe_Ribeir0]

Hi @nberumen 

Here is part of it, to make it look exact like your expected output, you need to apply some regex on some columns and rename the columns.

[nberumen]

Thank you Felipe! This helps out a ton and makes it easier for me to get to the outcome I need. I appreciate the help!

[Felipe_Ribeir0]

Hi @nberumen 

Just to follow up, it worked?