Splitting cells using formula; RIGHT/LEFT not applicable

Hi,
I use a web query and need a formula which would enable me to automatically
split cells into 2 and 3 cells.

The data looks like this "24-10" or "24-20-2" or "9-8-1" i.e. it's apperance
is inconsistent as to the lenght of the numbers but always separated by "-".

Text to columns is the obvious solution here but this is a web query and a
lot of sheets involved so something automated would be preferred.

Any help is appreciated
Regards

Mitch

I think you will need to combine, left, mid and right with the find
function.

You can get the first digit like this:
=LEFT(A2;FIND("-";A2)-1), assuming "24-10" is in A2.
If you cannot use extra columns the formulaes will get pretty long as
you move right.

Hopefully this will get you started.

Best regards
Per Erik



On Mon, 15 May 2006 10:59:03 -0700, Mitch
<Mitch@discussions.microsoft.com> wrote:

>Hi,
>I use a web query and need a formula which would enable me to automatically
>split cells into 2 and 3 cells.
>
>The data looks like this "24-10" or "24-20-2" or "9-8-1" i.e. it's apperance
>is inconsistent as to the lenght of the numbers but always separated by "-".
>
>Text to columns is the obvious solution here but this is a web query and a
>lot of sheets involved so something automated would be preferred.
>
>Any help is appreciated
>Regards
>
>Mitch

Thanks for your reply Per Erik

It got me started but I'm stuck again with where to go next. I can use as
many columns as needed as long as I get this right once and for all. Thanks
again

Regards

Mitch

Assuming data in A1:

in B1:

=LEFT(A1,FIND("-",A1)-1)

in C1:

=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))=2,MID(A1,LEN(B1)+2,FIND("-",A1,LEN(B1)+2)-(LEN(B1)+2)),RIGHT(A1,LEN(A1)-(LEN(B1)+1)))


in D1:
=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))=2,RIGHT(A1,LEN(A1)-(LEN(B1)+LEN(C1)+2)),"")

Not very elegant but works (I think!)

HTH

"Mitch" wrote:

>
> Thanks for your reply Per Erik
>
> It got me started but I'm stuck again with where to go next. I can use as
> many columns as needed as long as I get this right once and for all. Thanks
> again
>
> Regards
>
> Mitch

Toppers wrote...
>Assuming data in A1:
>
>in B1:
>
>=LEFT(A1,FIND("-",A1)-1)
>
>in C1:
>
>=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))=2,
>MID(A1,LEN(B1)+2,FIND("-",A1,LEN(B1)+2)-(LEN(B1)+2)),
>RIGHT(A1,LEN(A1)-(LEN(B1)+1)))

Could be shortened to

=MID(A1,LEN(B1)+2,FIND("-",REPLACE(A1&"-",1,LEN(B1)+1,""))-1)

>in D1:
>
>=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))=2,
>RIGHT(A1,LEN(A1)-(LEN(B1)+LEN(C1)+2)),"")

Could be shortened to

=MID(A1,LEN(B1)+LEN(C1)+3,LEN(A1))

Thanks Harlan .... I was sure mine could be improved!

"Harlan Grove" wrote:

> Toppers wrote...
> >Assuming data in A1:
> >
> >in B1:
> >
> >=LEFT(A1,FIND("-",A1)-1)
> >
> >in C1:
> >
> >=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))=2,
> >MID(A1,LEN(B1)+2,FIND("-",A1,LEN(B1)+2)-(LEN(B1)+2)),
> >RIGHT(A1,LEN(A1)-(LEN(B1)+1)))
>
> Could be shortened to
>
> =MID(A1,LEN(B1)+2,FIND("-",REPLACE(A1&"-",1,LEN(B1)+1,""))-1)
>
> >in D1:
> >
> >=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"-",""))=2,
> >RIGHT(A1,LEN(A1)-(LEN(B1)+LEN(C1)+2)),"")
>
> Could be shortened to
>
> =MID(A1,LEN(B1)+LEN(C1)+3,LEN(A1))
>
>

I see that you already have recieved an answer which ig much better
than mine. Anyways:
I used the following columns i row 1:
Original datai in A
First dash in B
Second dash i C
First digits in D
Second digits in E
Third digits in F
B2= FIND("-";A2)
C2= FIND("-";$A2;B2+1)
D2= LEFT(A2;B2-1)
E2=IF(ISERROR(C2);RIGHT(A2;B2-1);MID(A2;B2+1;C2-B2-1))
F2 =RIGHT(A2;LEN(A2-C2))


And then you need check for errors in the column F.
I think I would go for Toopers solution which seems quite briliant.

Per Erik


On Mon, 15 May 2006 11:48:02 -0700, Mitch
<Mitch@discussions.microsoft.com> wrote:

>
>Thanks for your reply Per Erik
>
>It got me started but I'm stuck again with where to go next. I can use as
>many columns as needed as long as I get this right once and for all. Thanks
>again
>
>Regards
>
>Mitch