MrExcel.com - Your One Stop for Excel Tips & Solutions

Split one cell value into two.

Posted by Deepika on April 29, 2000 2:36 AM

Hi,
I want help in spliting one cell value to two.

For example:

If i have
# 45, Car Street, Suite # 42, San Jose, CA - 98123 in A1.
Then how can i put

# 45, Car Street, Suite # 42, into cell B1
and
San Jose, CA - 98123, into cell C1

Help me please

Deepika

Posted by Tom Morales on April 29, 2000 9:44 AM

Deepika -
If your initial format is always consistent (i.e., you're always breaking the string at the second comma) you can write a macro using the InStr, Left, Right, and Trim functions.

But is all your data in precisely the same format?
Tom

Posted by Celia on April 30, 2000 12:41 AM


Deepika
It is possible to do this with worksheet functions.

If you want to split the cell at the third comma (as in your example), the two hideous formulas shown below will do it. Strangely enough, these formulas were quite easy and quick to construct. However, there must surely be some shorter and less hideous formulas than these.

Put in cell B1 :-

=LEFT(A1,FIND(MID(MID(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)),FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))+1,LEN(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))-FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))),FIND(",",MID(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)),FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))+1,LEN(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))-FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))))+1,LEN(MID(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)),FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))+1,LEN(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))-FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))))-FIND(",",MID(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)),FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))+1,LEN(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))-FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))))),A1)-1)

Put in cell C1 :-

=MID(MID(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)),FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))+1,LEN(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))-FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))),FIND(",",MID(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)),FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))+1,LEN(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))-FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))))+1,LEN(MID(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)),FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))+1,LEN(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))-FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))))-FIND(",",MID(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)),FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))+1,LEN(MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1)))-FIND(",",MID(A1,FIND(",",A1)+1,LEN(A1)-FIND(",",A1))))))

Celia