• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting Whole Address to Multiple Cells

W

windyinnc

New Member
Darn if I cannot figure this out. I kindly ask for this forums collective "guru" knowhow. I use the =left and =right formula all the time but only for easy extractions nothing overly complex. I run many queries which report back the entire stores address within one cell. Is there a formula I can use which will extract the indv address, city, state, and zip into their respective column? For example the query I run reports back the full address which is found in cell (A2). I would like to extracte the data from (A2) into the respective cells (B2)Address only, (C2)City only, (D2)State only & (E2)Zip only.


A B C D E

1 Store Address (query raw data report) Address City State Zip

2 123 Candycane Ln: Gumdrop FL: 33443


Any help is so greatly appriciated and would be a huge time saver for me. Thank you again.
 
Instead of using left(),right() etc, you can use Text to Columns to split on the colon, then once more on the City/State part and split on 'space'.
 
Hi,


If you use a combination of the following you can get what you are after:


LEN(text)

FIND(text,within,start number))

MID(text, start number, number char)

LEFT(text,number char)

RIGHT(text,number char)


For the first field use the find formula and look for ":" (which I think is your seperator). This will return a number.You can then use this number in the left formula to extract the element you want.


For the second field use the len formula to see how long the text in the first field is, this will be the start number for a Mid formula. You can then carry on from here.


It is probably best if you have a play with these formulae to see how you can link them up. Alternatively load a file up and we can have a look.


Myles
 
Windyinnc

Try the following

B2 =+MID(A2,FIND(" ",A2)+1,FIND(":",A2)-FIND(" ",A2)-1)

CD =+MID(A2,FIND(":",A2)+2,FIND(":",RIGHT(A2,LEN(A2)-FIND(":",A2)))-5)

D2 =+MID(A2,FIND(":",A2,LEN(B2&C2))-2,2)

E2 =+RIGHT(A2,5)
 
Wow...what a great response. Thank you so much everyone. I am not sure how to load a file up so if I did it incorrectly please accept my apologies.


@ Hui - I took your suggestions and added the formulas to populate the results but there seams to be a few issues within the address and state columns. I may have not been descriptive enough with address only but I wish to include the numbers preceding the address as well. The states for the most part are correct except for a few. I really do appreciate all the help. I'm more or less self taught (mostly from this great site) on excel but for some reason I'm not grasping this one.


http://www.mediafire.com/?j2nv4fwyljn
 
@ TessaES - thank you for the "duh" moment with using text to columns. It's a great suggestion.


@ Myles - thank you for the suggestion as well. I know how to use the Left, Right & Mid formulas but only in the most simplistic way. I'm in need of more study to better grasp the way Hui used them.


So much to learn...only wish I had more time to invest. Oh well...a little at a time over the long run will add up.


Best regards and thank you for all the help.
 
Hui

Thank you very much...works like a charm and your help saved me a lot of work. Much appreciation for this board and the contributors.
 
You must log in or register to reply here.
Back
Top