Create a UDF that uses VBA's version of Trim which does not touch the inner spaces. Only removing the leading and trailing spaces
Function MyTrim(str As String) As String
MyTrim = Trim(str)
End Function
Then you can call it from the worksheet:
=MyTrim(A1)
If you want a formula to do it:
=MID(LEFT(A1,AGGREGATE(14,6,ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)))/(MID(A1,ROW($XFD$1:INDEX(XFD:XFD,LEN(A1))),1)<>" "),1)),AGGREGATE(15,6,ROW($XFD$1:INDEX(XFD:XFD,LEN(A1)))/(MID(A1,ROW($XFD$1:INDEX(XFD:XFD,LEN(A1))),1)<>" "),1),999)
