How can I remove ONLY top and trailing spaces, leaving spaces between words alone with an excel formula?

Question

In excel, it TRIM()removes all spaces before and after text, and also removes any duplicate spaces between words.

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

In the following example, I am looking for a formula that will execute the formula for the fictitious formula "WXYZ":

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

Please note that I read somewhere that TRIM()in VBA it will work the same as in WXYZ above. However, I am looking for a solution to the formula.

Answer 1

, ( A1):

=MID(A1, FIND(LEFT(TRIM(A1),1),A1), (LEN(A1)-MATCH(RIGHT(TRIM(A1),1),INDEX(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1),0),0)-FIND(LEFT(TRIM(A1),1),A1)+2)

FIND(LEFT(TRIM(A1),1),A1)

MATCH(RIGHT(TRIM(A1),1),INDEX(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1),0),0) .

Answer 2

.

, A1, B2. , ROW(B:B) , ( B ), , , , , .

=MID($A1,
FIND(LEFT(TRIM($A1),1),$A1),
1+MAX(ROW(B:B)*(ROW(B:B)<=LEN($A1))*(MID($A1,ROW(B:B),1)<>" "))
-FIND(LEFT(TRIM($A1),1),$A1))

- [Ctrl] + [Shift] + [Enter] , { } ,

, @Aakash, INDIRECT :

-ROW(INDIRECT("1:"&LEN($A7)))

:

-ROW(B:B)

, Non-volatile:

=MID($A1,
FIND(LEFT(TRIM($A1),1),$A1),
(LEN($A1)-MATCH(RIGHT(TRIM($A1),1),INDEX(MID($A1,LEN($A1)-ROW(B:B)+1,1),0),0)
-FIND(LEFT(TRIM($A1),1),$A1)+2))