Text to Columns - Formulas

You are using three different methods.

How would sa formula know which of the three methods to use?

Using Power Query in the attached file, you can see I achieved the second and third requests. The first column split is close and requires a bit of excel native reformatting after the PQ result.

Click on my signature Power Query links if you are unfamiliar with Power Query to understand this action.

Please try at
B5:G7
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A5,","," "),"-"," ")," ",REPT(" ",99)),COLUMNS($B5:B5)*99-98,99))

B14
=UPPER(LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A14,"An ",),"The ",),"A ",)," ",),5))

B20
=UPPER(TRIM(LEFT(SUBSTITUTE(A20,",",REPT(" ",9)),5)))

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.