Splitting cells using formula; RIGHT/LEFT not applicable
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))
|