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 |
