Excel trim function is removing spaces in middle of text - this was unexpected

Question

It was surprising (?) that the Excel trim function would remove spaces in the middle of text. I mistakenly believed that the excel trim function was used to remove leading and trailing spaces. For instance, "Last Obs Resp" becomes the value of a cell with the value =Trim("Last Obs Resp")

Sure enough, Microsoft documents it this way: https://support.office.com/en-gb/article/trim-function-410388fa-c5df-49c6-b16c-9e5630b479f9

I am used to the Oracle database trim function which only removes leading and trailing spaces. 

Was the excel Trim function always this way?

Excel does not have ltrim and rtrim functions.. i.e. I can't do: =RTRIM(Ltrim("Last Obs Resp"))

I wonder how I achieve the equivalent in Excel when I don't want to remove doubled-up spaces in the middle of the string.

Answer 1

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)