Calling all Excel wizards! I need help

Ender

I need you Excel geniuses to help me out here.

I have a cell that contains the street address, city, state, and zip for a list of customers. I need to separate that out to street address in one cell, and city, state, and zip in their own respective cells.

I.E.,

155 Any Road Austin TX 39934

to

155 Any Road

---

Austin

---

TX

---

39934
[edit: the dashes represent cells. A bunch of spaces doesn't work ]

The main problem is that the street addresses are of random lengths, in both pure number of letters, but also number of words. For example, a street address could be 155 Any Road Suite 1.

I've wracked my brains trying to figure out how to break this down in the easiest method possible. Anyone have any ideas?

Oh yeah, and VBA is not an option, because my bosses are cheapasses.

Any ideas would be great!

bigwah

Text to Columns. Should be under Data tab. Will be decently easy.

Ender

Yeah, I did try text to columns. The problem is that the only way to do it is by using Space as a delimiter, which means that any spaces between words becomes a new cell. This wouldn't be a problem, except that some of the street addresses are multiple words (155 Any Road versus 155 Any Road Suite 1), which makes for uneven column counts.

If I could figure out a way to use a formula to move to the last full column and pull that out, it would be easier, but I can't figure out how to write a formula that includes a formula as a cell reference, even under r1c1.

Some days, the restrictions on Excel drive me crazy. :P

I realize that this may be impossible, and if so, I'll accept that, it would just save me a ton of work if I could automate at least a chunk of the process.

Thanks again!

Dedian

Hmm, without some other delimiter than the space, it might be tough (text to columns won't work, as you've found out). Wonder if you could work from the right, instead? Are all these addresses in a single city, or at least, do all the cities have just one word in them? Then, at least you could get ZIP, State, and City, and assume the remainder is the address...

Ender

Yeah, unfortunately not. That's the way I began, but ran into a wall at the city, which is different for all of them, some of them being multiple words.

Ah well, guess it's Typingfest 2010 for me!

ecco the dolphin

Ender wrote: »

If I could figure out a way to use a formula to move to the last full column and pull that out, it would be easier, but I can't figure out how to write a formula that includes a formula as a cell reference, even under r1c1.

Use INDIRECT()

e.g.

=INDIRECT("A3") will return the contents of A3

Maybe use "COUNTA()" to count the non-blank cells

and go something like (assuming on row A):

Last filled column (in A1)
=COUNTA( A10:A20 ) + 10

Last column (in A2)
=INDIRECT( "A" & A1 )

I have to leave for work now, so I'm typing this quickly.

There may be errors!

Zeon

The easiest way to do it is going to be to write a VBA macro (or whatever theyre calling it in 2007/2010, i forget) that will parse the string with spaces as the delimiter, take the last entry (the zip), move it 3 cells over, take the new last entry (the state), move it 2 cells over, and finally take the new last entry (the city) and move it 1 cell over. Unfortunately its going to break when you get to a city like "New York" or "San Diego". You could fix it by having the third move read back to the next spaceband, and if the word is "New" or "San" or any other city name prefix ("El" comes to mind but im sure there are others), it includes that as well.

Theres no reason that your bosses can disapprove of VBA by being cheapasses, the macro editor is built in and included with every copy of Office sold. Theres no licencing fees or extra software to buy. Alt-F11 opens the macro editor. Save the results as a new template rather than inside the current spreadsheet if you want to be able to use it in other documents as well.

I could write this for you but im sure theres someone that can get it done way faster than i can, it takes me forever to write new VBA macros.

pirateluigi

It's easy enough to pull out the state and Zip, but without a way to delimit the city from the street, you'll have to do it manually. Unless you have a list of all possible city values? Then you would have some options.

Zip Code (if cell is in A1): = right(A1,5)
State = mid(a1,len(a1)-7,2)
Street + City =left(a1,len(a1)-9)
If City is Always One Word: (if Street+City is in cell d1): =right(d1,len(d1)-search("%",substitute(D1," ","%",Len(D1)-Len(Substitute(d1," ","")))))

Then everything else is just: (Assuming city is e1): =left(d1,len(d1)-len(e1)-1)

Easy Peazy

Ender

You guys are awesome. I haven't had a lot of time to mess with Indirect(), that may be a good way to get this done.

And durrrrrrrrr...I must have been thinking of another module. You're right, VBA is right there. Now to just teach myself how to use it. :P

I'll use your examples here as a good basis. Hopefully, when I have to do this again, I can have it all set up to do most of the work automatically!

Thanks homies!

wonderpug

Ender wrote: »

Ah well, guess it's Typingfest 2010 for me!

If you're going that route anyways, at least let Excel help minimize the gruntwork.

Do a text to columns as best you can. The majority should fall into four columns the way you want them to be. Sort the results by column 5, then 6, then 7, etc. to get all the good ones clumped together and similar bad results clumped together.

Now as you go through and fix the anomalies you can throw in a bunch of concatenates here and there to speed things up, like if you see a whole slew of "Los Angeles" addresses broken up in a row.

wonderpug

Oh, and don't forget to mark the zip code column as plain text. Seems common sense but time after time I see even tech savvy people have it escape their minds and all the 0XXXX zip codes get the first digits cut off.