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 believe this should work (assuming your string is located at 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) returns the location of the first non-space character in the string

MATCH(RIGHT(TRIM(A1),1),INDEX(MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1),0),0) returns the location of the last non-space character in the string from right-to-left.