Splitting cells using formula; RIGHT/LEFT not applicable

[Guest]

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

 

[Per Erik Midtrød]

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

 

[Guest]

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

 

[Guest]

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

 

[Per Erik Midtrød]

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

 

[Harlan Grove]

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))

 

[Guest]

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