Opposite of Concatenate
 

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?? All help is greatly appreciated

Steve

Datatext to columns

--
Regards,

Peo Sjoblom

(No private emails please)


"Steve" wrote in message
...
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?? All help is greatly appreciated

Steve

Steve wrote:
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)