need to separate addresses

[dilleyo725]

Hello,

I'm working with a pre-existing list of vendors and currently the addresses for each are in one column. I would like to separate by city, state, zip. I was thinking of text to columns but since the addresses are not all aligned that won't work.

Any suggestions?

thanks!

 

[adept]

How are they separated?

 

[dilleyo725]

space betwee the physical address and city, then comma between city and state.

many inconsistencies with the format. Guessing multiple people were inputting originally.

123 hope st. cerritos, ca 90703

155 sad ave. long beach ca 90805

 

[adept]

I usually start at the left with the ZIP (that is usually 5 chars) and then separate the following a bit at a time.

=right(A1,5)

Perhaps you can post a sample of your data?

Craig

 

[dilleyo725]

can I just copy and paste a sample straight from the spreadsheet?

 

[adept]

Look at this Sticky post on how to post spread sheets to the board.
http://www.mrexcel.com/forum/showthread.php?t=89356

 

[adept]

Maybe this is a more useful approach to posting to the board.
http://www.excel-jeanie-html.de/index.php?f=1

 

[dilleyo725]

the "right" formula works for zip code but when it comes to city, it gets more complicated because even if I try using a "mid" formula, essentially that would mean I would have to count each character per line entry...correct?

Is there another way?

 

[adept]

That's right.
I use formulas, don't know VB well enough.
I start from the known lengths (ZIP) and move in from the right.

Is the state always 2 chars?

Looks like the city and state are separated by a comma, sometimes. I would replace the commas with "" to delete them.

Using mid() you can work it out.

There are many smarter folks on the boards than me so I am awaiting to see what any of them can supply.

 

[dilleyo725]

No worries. I appreciate your attempt to help.