Excel 2007 text to column help

November 30, 2010 2:50 PM

Excel 2007 text to column help.

I have 115 cells in column A with values like:

38823 2nd Street E Palmdale CA 93550
3858 Le Sage St Lynwood CA 90262
1460 F. Street Reedley CA 93654
254 N. Alta Ave Dinuba CA 93618


I need these to go into columns for number, street, city, state and zip. I tried text to column, but since they are all different lengths, and some cities or streets have 2 or 3 words in them, and are not delimited by a symbol, I cant use "fixed width" or "delimited". Are there any other options?

posted by Ezrie to Computers & Internet (19 answers total)

Are you comfortable using cell formulas? You can put the unparsed data into Column A and then in each of Columns B, C, and D pick out substrings using Excel's string functions.
posted by JohnFredra at 2:55 PM on November 30, 2010

So, if you have all that in column A
Put this formula in column B:
=FIND(" ",A1,1)
And this formula in column C:
=LEFT(A1,B1-1)
posted by found missing at 2:56 PM on November 30, 2010

You're probably going to wind up writing a macro. If you don't have experience with that, you can learn pretty quickly, but it might be easiest to enlist the face-to-face help of someone with some programming experience.

Assuming everything is in the number-street-state-zip format, you can probably do this with a regular expression like /(\d+)\s(.*?)\s([A-Z]{2})\s(\d{5})/ and then pump the matches for each sub-match into new columns.
posted by colin_l at 2:58 PM on November 30, 2010

That will give you the street number.
Then, put this formula in column D:
=RIGHT(A1,5)
That will give you the zipcode
posted by found missing at 2:58 PM on November 30, 2010

Then put this formula in column E:
=Len(a1)
This formula in column F:
=len(c1)
And, finally, this formula in column G:
=MID(A1,B1,E1-5-F1)
posted by found missing at 3:01 PM on November 30, 2010

taa dah
posted by found missing at 3:01 PM on November 30, 2010

Alternative:
Column A: raw data
Column B: =LEFT(A1, SEARCH(" ", A1) - 1)
Column C: =SUBSTITUTE(SUBSTITUTE(A1, B1, ""), D1, "")
Column D: =RIGHT(A1,5)
posted by JohnFredra at 3:04 PM on November 30, 2010

change my finally formula to:
=MID(A1,B1+1,E1-7-F1)
to get rid of spaces
posted by found missing at 3:05 PM on November 30, 2010

John's is more elegant
posted by found missing at 3:06 PM on November 30, 2010

TRIM() will do that for you, found missing
posted by JohnFredra at 3:07 PM on November 30, 2010

With white space cleaned up:

Column A: raw data
Column B: =LEFT(A1, SEARCH(" ", A1) - 1)
Column C: =TRIM(SUBSTITUTE(SUBSTITUTE(A1, B1, ""), D1, ""))
Column D: =RIGHT(A1,5)
posted by JohnFredra at 3:08 PM on November 30, 2010

Wow.. I just realized I completely misread your question. My solution doesn't solve your problem.
posted by JohnFredra at 3:12 PM on November 30, 2010

damn
posted by found missing at 3:14 PM on November 30, 2010

Well, you can peel off the state in the same way, but that leaves the problem of the city, given that it can be more than one word.
posted by found missing at 3:15 PM on November 30, 2010

This question is similar, though it also includes names on the address line. There's a macro in there that will probably have to be modified somewhat because of that, but it may help. (Sorry, don't have time right now to update it. If I have time later, I'll give it a shot.)
posted by inigo2 at 3:17 PM on November 30, 2010

On my computer, I would copy the items from the column to NoteTab, a text manager, and then replace every instance of "CA" with "CA" followed by a tab code. I would do other replaces similarly, then I would go through and fix all of the false-positive errors manually. (There are only 115 listings, so there would not be many.) Then I would do an import (or paste) as a tab-delimited text file.
posted by yclipse at 3:33 PM on November 30, 2010

Assuming your data is in Column A:

Get the house number with this formula in B1 =LEFT(A1,FIND(" ",A1)-1)

And get the street and city all at once by putting this in C1:
=MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1)-9)

Pull out the State and ZIP by using this formula in cell D1 =RIGHT(A1,8)
You can then use Delimited, Space to separate them.

You'll have to do the division between Street and City yourself to be sure it is accurate. I would go through each cell and add a comma or other mark to divide them and then use Delimited, Comma to separate them all at once.
posted by soelo at 3:56 PM on November 30, 2010

You only have 115 items. It will cost you less time to hand-insert delimiters into those than to write and check code to do it for you. So, don't bother with a coded solution unless you are going to get lots more addresses and have no way to import them in a more structured form.
posted by flabdablet at 4:42 PM on November 30, 2010 [3 favorites]

If you download the text editor Notepad++, you can use the following regular expression to edit your file and insert separators.

Do a find-and-replace with Search mode set to Regular expression, with this as the Find term:
([^\ ]*)\ (.*)\ ([^\ ]*)\ ([A-Z][A-Z])\ ([0-9][0-9][0-9][0-9][0-9])
and this as the Replace term:
\1; \2; \3; \4; \5

This will insert a semicolon after the number, street, city, and state, turning your example data into:
38823; 2nd Street E; Palmdale; CA; 93550
3858; Le Sage St; Lynwood; CA; 90262
1460; F. Street; Reedley; CA; 93654
254; N. Alta Ave; Dinuba; CA; 93618
which you can then import straight into Excel.

Note that this assumes that the city is just one word; for a multi-word city the semicolon will go in the wrong place, and you'll have to manually edit it. But skimming through the file looking for and fixing these few errors is still much less work that putting in all the separators by hand.
posted by logopetria at 12:00 AM on December 1, 2010