I need a Text Formula....Easy (Hopefully) 1

[rhoneyfi]

I have a very large Excel Spreadsheet that has the names and addresses of our clients. The "Address" field contains the city, state, and zip all in the one field. I need to split this field so that the City, State, and Zip are in there own seperate new fields called "City", "State" and "Zip". Any ideas how to do this?? Thanks

 

[Hayton]

Hi, are the City, State & Zip seperated in the string and if they are is it with a space or comma.

 

[Hayton]

Try these formulas. I assume that your string is in cell AI.

For City use:
=LEFT(A1,FIND(" ",A1)-1)

For State Use:
=IF(ISERR(MID(A1,FIND(" ",A1)+1,IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)),FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1)),"",MID(A1,FIND(" ",A1)+1,IF(ISERR(FIND(" ",A1,FIND(" ",A1)+1)),FIND(" ",A1),FIND(" ",A1,FIND(" ",A1)+1))-FIND(" ",A1)-1))

For Zip use:
=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ","&quot))))

If you have a 'comma' seperating the elements of the string then substitute " " with "," (ie no spaces).

I hope that this helps you.

 

[ETID]

Alternate,...

Highlite the col

then from menu

Data>text to columns....follow the wizard from there.


also if you run the macro recorder during this process, you will have generated the code needed to do this.

alt+f11 to view after you stop the process

 

[AFTech81]

I agree with ETID. The text to columns would be the easiest way to go about it.

Brian
USAF
Network +, Win2k Pro

 

[petrosky]

Well done ETID,

I have used left, right & len before but your solution suits my needs.

Peter

Remember- It's nice to be important,
but it's important to be nice

 

[rhoneyfi]

Thanks for all the help everyone! The Data --> Text to Columns worked great!