Match or Lookup Formula For Address Spreadsheet

[jchapman74]

I am having a tremendously hard time trying to locate information on exactly what I am trying to do. Any help would be greatly appreciated.
The Problem:
I have a need to enter large amounts of USPS Addresses into a specific format. I am trying to automate this process if possible. The biggest problem of course is that the data I receive is never formatted correctly. For instance, I need to format the following addresses...
21715 Filigree Ct. Ashburn VA 20147
901 E St NW Washington DC 20004
706 Giddings Ave. Annapolis MD 21401
149 State St. Montpelier VT 05602
730 Peachtree St. NE Atlanta GA 30308
44 East Avenue Austin TX 78701

Into the following format...
addresses.png

I'm not even sure if this is possible but I would like to find a way to separate all the fields into the appropriate columns automatically. I have even gone as far as adding a tab that has all of the US cities and states in case I need to do some sort of lookup.

Please help!

Thanks

[oeldere]

You get better help if you post an excel example without confidentional information.

[Richard Buttrey]

Hi,

Are the addresses you show all in a single column of cells?
If so you could start by using Data Text To Columns, using the space character as a separator, to split them into columns.

Then you'd need to identify those rows which only have 6 values and move the last three fields across by one field. However whilst this will work with your limited example, if your actual data has different formats then you may find it doesn't work and you'll need to identify the differences and apply some similar corrrections.

[jchapman74]

Thanks for the response. I have tried exactly that and the data varies so much that it doesn't always work.

[Richard Buttrey]

That's what I thought. Can you identify the different conditions then filter the data to display all relevant rows and change them en bloc. Then pick the next condition and repeat until you've changed them all.

[oeldere]

See this file, maybe it helps.

[jchapman74]

That's what I thought. Can you identify the different conditions then filter the data to display all relevant rows and change them en bloc. Then pick the next condition and repeat until you've changed them all.

Forgive my ignorance but I'm not sure I understand what you mean. Are you referring to a macro or something?

Thanks

[jchapman74]

Thank you so much for the file. Unfortunately, it needs to be able to break out if there is a E/W direction etc. This is the part that has me the most confused. I was thinking if there was a way I could create a formula that would look at a different tab and search for a string (city, state, direction) and then populate it in the appropriate column that might work. For instance, if the address is in Atlanta, there would be a lookup against the city list I have in sheet 2 and then Atlanta would be populated in the City column. I hope I'm making sense here.

[oeldere]

It could be done by this actions (i nummered them).

See if you can do it this way.

Please reply if you can use it.

[Kevin UK]

Hi jchapman74

Have you tried a Google search for Macros, plenty of them out there.

[jchapman74]

Oldere, thank you so much! I think this will work! I am going to try it with a larger sample and report back to you.

[oeldere]

Thank for the reply (for now).

I hope it will help you.

[jchapman74]

Oeldere,
I have been working on this the past couple days trying to figure out how to insert your formulas into the spreadsheet I have started. It doesn't seem to be working due to cells contents being a formula instead of data. Any ideas? I have attached what I am working on.

Thanks,

[oeldere]

Maybe, you can add the data in the file.

After that show us, in the same file, the wanted result.

[jchapman74]

Attached is how it is working and how I need it to look. Thank you for all your help.Address Separator Spreadsheet-Test2.xlsx

[oeldere]

See if you also get the data in the bleu cells if you run macro 1.

I am not that good with macro's, but just take a look (test) if this is what you're up to.

Then someone else can clean it (the macro) up.

[jchapman74]

Oeldere, I have not had a chance to do any more testing with this. I've been tied up on a project. I will let you know when I am able to test. Thanks again for your help.