View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default Splitting cells using formula; RIGHT/LEFT not applicable

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