Splitting cells using formula; RIGHT/LEFT not applicable

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mitch - 15 May 2006 18:59 GMT

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

Reply to this Message

Per Erik Midtrød - 15 May 2006 19:25 GMT

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

>Hi,
>I use a web query and need a formula which would enable me to automatically
[quoted text clipped - 10 lines]
>
>Mitch

Reply to this Message

Mitch - 15 May 2006 19:48 GMT

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

Reply to this Message

Toppers - 15 May 2006 20:35 GMT

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

> Thanks for your reply Per Erik
>
[quoted text clipped - 5 lines]
>
> Mitch

Reply to this Message

Harlan Grove - 15 May 2006 23:33 GMT

Toppers wrote...

>Assuming data in A1:
>
[quoted text clipped - 7 lines]
>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))

Reply to this Message

Toppers - 16 May 2006 07:46 GMT

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

> Toppers wrote...
> >Assuming data in A1:
[quoted text clipped - 21 lines]
>
> =MID(A1,LEN(B1)+LEN(C1)+3,LEN(A1))

Reply to this Message

Per Erik Midtrød - 15 May 2006 21:01 GMT

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

>Thanks for your reply Per Erik
>
[quoted text clipped - 5 lines]
>
>Mitch

Reply to this Message