Excel cells

Question

QUESTION: I have a first and last name, street address, city, state and zip all in column A1 (for example, Bill Clinton 7689 Park St Chicago, IL 66578). I wish to split the full name into one cell, the street address into another separate cell and the zip into another separate cell. I have a huge spreadsheet I am trying to clean up for a nonprofit agency and the entire thing is like this (One cell for each full name and address!!)! Please advise.

ANSWER: Your data is arranged in such a way that doing what you way is pretty intricate, but this does what you asked. in B1 enter this formula via ctrl+shift+enter, not just enter: =LEFT(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(1:100),1)),0)-2)

in C1 enter this formula via ctrl+shift+enter, not just enter: =LEFT(MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(1:100),1)),0),100),LEN(MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(1:100),1)),0),100))-6)

in D1: =RIGHT(A1,5)

HTH

---------- FOLLOW-UP ----------

QUESTION: When you stated to enter it via cntrl+shift +enter¡­.does that mean that I must hand type those formulas into the cells you requested then press cntrl+shift +enter? If yes, then what must I do after that? Will those formulas automatically separate out the info in all my cells below in the spreadsheet? So that the person¡¯s name falls in one cell by itself, the street address in one cell by itself and the city and state in one cell by itself and the zip in one cell by itself? (NOTE: I tried the copy/paste command for placing the formulas, but that didn¡¯t work.) THANKS!

Answer

You've changed the request. In your original post therewas no separation of address and city/state. My solution gave 3 columns: Name Address Zip

You can copy/paste the formulas in, but before pressing enter, hold ctrl+shift, THEN press enter.

There's no way to tellto split 7689 Park St Chicago, IL into 7689 Park St Chicago, IL Unless you can guarantee there will ALWAYS be 4 spaces so I'd know to split it on the 3rd space. Some cities have 2 names in them, like San Francisco; some addresses have 4 names in them, like 194 White Oak Street, so there's no consistent rule to apply.