Splitting cells using formula; RIGHT/LEFT not applicable

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