Text to columns separated by "-" using formulas

Hi,

Please see the attached spreadsheet.

I basically have 3 or 4 words separated by "-" in one cell.

In this example, I used the words Beg-Mid-End-End and Beg-End-End

I want to separate them to 3 different columns: Beg, Mid, and End-End.

I want create a formula, where the excel sheet could recognize when there is no "Mid", the cell should be blank, and the "End-End" should be together in the third column.

The formula I created (found online actually...haha) works well when there are 4 words but not work when there are 3 words. Please see the attached spreadsheet to see where I'm stuck and what I desire.

Again, when I drag down the formula for the Mid it should be blank when there are 3 words, and the End-End should be together on the last column.Thank you for your help!

Try this ...

C2:

=IF(COUNTIF(A2,"*Mid*")=0,"",TRIM(MID(SUBSTITUTE(A2,"-",REPT(" ",200)),200,200)))

D2:

=RIGHT(A2,LEN(A2)-LEN(B2&C2)-1-(C2<>""))