text-to-columns problem

**cuppingmaster** · 04-16-2007, 11:28 PM

Hi,

So I'm making a mail merge and the person who entered the address data on excel didn't make a seperate column for city, state, zip (meaning I have street number, address, and city, state, zip all in the same cell!). I want it to move city, state, zip to a new column so the merge can format to a three-line address properly but without good delimiting I don't know how to do it cleanly.

Anyone know how to do it? I kinda think Excel can recognize an address and autoformat properly but I don't know how to tell it to do so.

Thanks!

**Paul** · 04-17-2007, 12:06 AM

That I know of, Excel doesn't recognize addresses. You need to use formulas or vba code to separate out the parts.

The problem is, if you don't have a consistent delimeter, or something other than a space, you're in for a long night. If you have something like:

1 East 42nd St., New York, NY, 11201

Then it's easy enough to separate using commas as the delimeter. If, however, you have something like:

1 East 42nd St. New York NY 11201, the only think you can hope to accomplish is use text to columns using spaces and then recombine and shift data manually.

If all your entries ended in 5-digit zip codes you could easily pull that out. Same goes for states (if they're all two char. abbreviations).

Street and City, however, would be ugly, since there can be many parts to a street address, and anywhere from 1-4 parts of a city name.

Does this make sense?

**cuppingmaster** · 04-17-2007, 12:13 AM

Yeah, I have something like the second condition you described. I guess next time I should prep the Excel sheet myself!

I didn't think about just using the spaces as a delimiter and then recombining, but that's not much better than exporting the merge to word and manually reformatting. Luckily the list is only 340 names... rather than 10000 plus I used to work with.

Thanks for the help!

text-to-columns problem

LinkBack

Thread Tools

Rate This Thread

Display

text-to-columns problem

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions