Splitting Text Cells

Posted by Roy Brunt on August 16, 2001 12:35 PM

I have the following text in cells and need some vb code or a formula to split it:

as25(nec20)
aes12(als30)

I would like to split the cells above so they read as follows

as25 nec20
aes12 als30

i can get them to split into two cells but not remove both the brackets from the right hand side.

Please help with this if you can

Thanks
Roy

Posted by Sean Tobin on August 16, 2001 12:46 PM

If A1 contains the original information:

A1= as25(nec20)
B1= =FIND("(",A1)
C1= =LEFT(A1,B1-1)
D1= =FIND(")",A1)
E1= =MID(A1,B1+1,(D1-B1)-1)
F1= =C1& " " &E1

F1 will give you the format the way you want it. You can then hide cols ABCD&E.

I'm sure there's a better way to do this but this works.

Posted by IML on August 16, 2001 1:04 PM

OR...

You could also use find and replace. Find "(" and replace with a space. Find ")" and replace with nothing.

by formula you could use
=LEFT(A1,FIND("(",A1)-1)&" "&MID(A1,FIND("(",A1)+1,LEN(A1)-FIND("(",A1)-1)

assuming your needing for cell a1.

good luck

Posted by Eric on August 16, 2001 1:16 PM

Here's another formula to split

If your original data is in a1, then in b1 type
=left(a1,find("(",a1)-1)
In c1 type
=mid(a1,find("(",a1)+1,4)
if the text between parentheses is always 5 characters
if the length of text between parentheses varies, try typing the following into c1
=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)
Lots of ways to do this, hope this one helped