CODEWITHSUNDEEP
excel-formula
Allan F
Allan F

Reputation: 2298

excel trim function is removing spaces in middle of text - this was unexpected (?)

The excel trim function is removing spaces in middle of text - this was unexpected (?) i.e. I thought that the excel trim was for trimming leading and trailing spaces. e.g. a cell value of =Trim("Last Obs Resp") becomes a value of "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. https://www.techonthenet.com/oracle/functions/trim.php

Was 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?

This page documents VBA trim function: https://www.techonthenet.com/excel/formulas/trim.php

Upvotes: 2

Views: 1800

Answers (1)

Scott Craner
Scott Craner

Reputation: 152535

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)

enter image description here

Upvotes: 3

Related Questions