The state and zip are easy because they are always located at the end of the
text... the zip is the rightmost 5 characters and the state is always 2
character long starting at the 8th character from the end.

Zip: =RIGHT(A1,5)

State: =LEFT(RIGHT(A1,8),2)

--
Rick (MVP - Excel)


"Craig860" wrote in message
...
Rick,
Thank you so much. I'm gonna right this formula on the black board and
probably stare it for the next month or so. I am grateful.
May I ask how you would write this for state and zip?

"Rick Rothstein" wrote:

Try these formulas in the indicated cells and then copy them down...

B1:
=IF(LEN(A1)-LEN(SUBSTITUTE(A1,",",""))=3,TRIM(MID(A1,FIND(",", A1)+1,FIND(",",MID(A1,FIND(",",A1)+1,99))-1)),"")

C1: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,LEN(A1)-10),",",REPT(" ",99)),99))

--
Rick (MVP - Excel)


"Craig860" wrote in message
...
Ok brain cramp. I'm going to the Lords of Excel for help with this one.

Lets say:
a1=238 Woodbridge Terrace, Apartment 6,South Hadley, MA 01101
a2=123 Anystreet Rd,Boston, MA 06095
a3=490 Elms St.,Apt 6,Hartford, CT 06090

I'd like to get for results in:
B1=Apartment 6
B2=<blank
B3=Apt 6

As a bonus id like to get
C1=South Hadley
C2=Boston
C3=Hartford

You can probably see by now that source data is inconsistent where not
every
line will have an "Apartment" or "Apt". Or am I asking for too much out
of
excel? Any help is appreciated.