Steve said:
I use the Concatenate function all the time. Is there a function that does
the opposite? For example one cell has all three City, State and Zip info.
I need each of these all in their own cells. Is there a function that does
this??
You don't state the rules for splitting up the City/State and Zip info.
Are the values separated by an obvious delimiter (eg. a comma)?
If so, you can use the Left, Mid and Right worksheet functions along
with Find or Search. For example, if cell A1 contains:
Beverly Hills, California, 91210
Then in B1 you could try:
=LEFT(A1,FIND(",",A1)-1)
In C1:
=MID(SUBSTITUTE(A2,B2,""),3,FIND(",",SUBSTITUTE(A2,B2,""),2)-3)
In D1:
=RIGHT(A1,LEN(A1)-FIND(",",A1,FIND(",",A1)+1))
Not particularly elegant, but it seems to work. Of course if you can
afford to use a few extra cells to hold intermediate values, you can
simplify these quite a bit. For example,
In B1:
=LEFT(A1,E1-1)
In C1:
=MID(A1,E1+2,F1-E1-2)
In D1:
=RIGHT(A1,LEN(A1)-F1)
In E1:
=FIND(",",A1)
in F1:
=Find(",", A1, E1+1)