Pulling company name, street address, city, state, and zip from address

Hello all,

I am working on a "find a retailer" app for my company's webpage and a manager at the company [won't say name] gave me their excel file from another application and all of the addresses are in the format below:

R & R FLOORING PO BOX 426 ALDEN, MN 56009

Name Address1 Address2 City State Postal code Country Phone <-- this is the required format for the information i need for list


I am not very proficient with Excel although I found some codes/macros on this forum that allowed me to whittle most of the content to just the street, state, and zip. I still need to pull the company name out as well as the state without the tagalong numbers that come from the street in the address. I am attaching my file so you guys can see what I'm working with here and see what I have done.

P.S I have done some searching on the forum and can only people with no solution but I'm hoping someone can work with what I've whittled the given information down to. Any help will be greatly appreciated!

This file is not perfect... but it should get you going and it might be a bit easier to work with.
I wish I had more time to work on it, but right now I do not.

Not all of the cities are correct, specifically the ones with two or more words in them. (Dollar Bay, MI, Hilton Head SC, Sun City West, for example.)

The Canada addresses are not correct.

But you said "any help" appreciated and I think this is more than what you had.

Yes that is definitely an improvement on what I had, thanks for your help!

Try the attached.
This is not perfect but I hope this could reduce your work...

Thanks Jindon, much JDM love

I just realized that I forgot to include the phone numbers for the company corresponding with the address info. I hate to ask this after getting immediate help with what I had but could anyone help with this file. I was going to copy and paste them but then i realized they don't match up correctly because some companies aren't there. Mostly because they were names I guess. Thanks for any help ahead of time, you guys are the awesome!

Your Addresses2.xlsx file, with Jindon's macro (with a few additions by me). No rows were removed.

addresses3.xlsm

Thanks for the help, what language are you coding in here?

Originally Posted by Ed_Collins

Your Addresses2.xlsx file, with Jindon's macro (with a few additions by me). No rows were removed.

Attachment 406480

Originally Posted by jindon

Just a little alteration...
Regarding address with PO Box, it is the best I can do.
It will be more accurate if StAdd can be refined/added items properly.
However, I don't think it can be perfect...

Please Login or Register  to view this content.

Thanks so much for both of you guys' help! This is an awesome place for an excel novice.