MS Excel Reordering letters in cell

Question

I've got a column of cells with the following information in them: ABCDP2S6 EFGHP2Te6 X YY.Y ZZZ

To output the following using a formula command: CDABP2S6-GHEFP2Te6

I looked it out on Google and it appears to include REPLACE and FIND, but I can't figure out how to do it correctly.

Answer 1

Use TEXTBEFORE() function.

=TEXTBEFORE(A1,"_X")

For older versions, use-

=LEFT(A1,SEARCH("_X",A1)-1)