How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?

Question

In excel, TRIM() will remove all spaces before and after text, while also removing any duplicate spaces in between words.

Is there a formula or combination thereof that will do the same as TRIM() but leave spaces between words as-is?

In the following example, I'm looking for a formula that will accomplish that of the fictitious formula "WXYZ":

TRIM(" Omicron Persei 8 ") = "Omicron Persei 8"
WXYZ(" Omicron Persei 8 ") = "Omicron Persei 8"

Note that I've read somewhere that TRIM() in VBA will work like that of WXYZ above. However, I'm looking for a formula solution.

Answer 1

I tried the answer posted by @JvdV. It removed leading and trailing spaces from a value that had spaces in the middle to begin with and preserved all middle spaces (which is great).

But, for a value with no spaces in the middle to begin with, the formula returned an error. So, I added to it. I took the formula from @JvdV and put it inside an IF() that tests for the presence of middle spaces. If there are middle spaces, it runs the formula, but if not, it simply runs TRIM().

=IF(ISNUMBER(FIND(" ",TRIM(A2))), LET(x,TEXTSPLIT(A2," ",,1),TEXTJOIN(DROP(DROP(TEXTSPLIT(" "&A2&" ",x),,1),,-1),,x)), TRIM(A2))