1

The TRIM function eliminates duplicated spaces inside the string as well as trimming off leading and trailing spaces. I need to trim off leading and trailing spaces, but leave duplicated spaces in the middle of the string alone.

Is there a way to do this in an Excel formula?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Shavais
  • 2,476
  • 1
  • 27
  • 25

2 Answers2

2

Not sure if that's easily done with just formula. This VBA will do the job:

Public Function TRIMLR(Target As Range) As Variant

    TRIMLR = RTrim(LTrim(Target.Value))

End Function

As pnuts says in his comment - this will do the job:

Public Function TRIMLR(Target As Range) As Variant

    TRIMLR = Trim(Target.Value)

End Function

Have now found a couple of formula at this website, just need to figure out how to combine the two formula:
http://eforexcel.com/wp/article-18-ltrim-and-rtrim-through-excel-formulas/
LTRIM:
=REPLACE(A1,1,FIND(LEFT(TRIM(A1),1),A1)-1,"")
RTRIM:
=IFERROR(REPLACE(A1,MAX(INDEX((MID(A1,ROW(A1:INDEX(A:A,LEN(A1))),1)<>" ")*ROW(A1:INDEX(A:A,LEN(A1))),,))+1,LEN(A1),""),"")

Darren Bartrup-Cook
  • 18,362
  • 1
  • 23
  • 45
  • 1
    Dammit..... I knew they existed.... I go confused as I was searching for trimleft and trimright. You can also just nest them in the formula as well `LTRIM(RTRIM(range))` – Matt Oct 16 '15 at 16:03
  • My Excel is not recognizing LTRIM or RTRIM. The Excel I have is part of MS Office Pro Plus 2010. – Shavais Oct 16 '15 at 16:13
  • After briefly skimming over https://msdn.microsoft.com/en-us/library/office/ee814737(v=office.14).aspx, I successfully added this function to my sheet. If, after a day or two, no one comes up with an answer that doesn't require vba, I'll mark this as the accepted answer. – Shavais Oct 16 '15 at 16:21
  • I notice that simply saying TRIMLR = LTrim(RTrim(Target.Value)) in the VBA function also works for me. – Shavais Oct 16 '15 at 16:28
  • Yep, well spotted @Matt. I've updated the code to reflect that. I'm sure there's a non VBA answer, but it's 17:30 on a Friday. Might have a think about it over the weekend. – Darren Bartrup-Cook Oct 16 '15 at 16:30
  • 1
    Pnuts is correct, the VBA `Trim` function performs just as the asker needs, as a matter of fact to trim all duplicated spaces in VBA the command to use is `Application.Trim(string)` or `WorksheetFunction.Trim(string)`. Just try this: `Const kTest = " Trim this now "` `Debug.Print "[" & kTest & "]"` `Debug.Print "[" & Trim(kTest) & "]"` `Debug.Print "[" & Application.Trim(kTest) & "]"` `Debug.Print "[" & WorksheetFunction.Trim(kTest) & "]"` – EEM Oct 17 '15 at 11:21
  • Well I'll be.... you're right @pnuts. Tried to down vote the answer, but can't on my own answer. You'd best add it as an answer so it can become the accepted answer. – Darren Bartrup-Cook Oct 19 '15 at 07:52
2

Found out the hard way only yesterday that Excel's TRIM eliminates consecutive spaces within the string. Out of sheer annoyance I came up with a way of implementing standard trim() using only worksheet functions (with some help from this answer). It's not pretty though:

=MID(A1,FIND(LEFT(TRIM(A1),1),A1),FIND("@",SUBSTITUTE(A1,RIGHT(TRIM(A1),1),"@",LEN(A1)-LEN(SUBSTITUTE(A1,RIGHT(TRIM(A1),1),""))))-FIND(LEFT(TRIM(A1),1),A1)+1)

(if your text happens to contain @, then use a different unusual character in the formula)

Explanation:

  • LEFT(TRIM(A1),1) gives the first non-whitespace character, so FIND(LEFT(TRIM(A1),1),A1) gives the position of this in the original string

  • as per the linked answer, this would find the last \ in the string. =FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\",""))))

    Replace \ in the forumla with RIGHT(TRIM(A1, 1)), and you find the location of the last non-whitespace character in the original string, i.e. FIND("@",SUBSTITUTE(A1,RIGHT(TRIM(A1),1),"@",LEN(A1)-LEN(SUBSTITUTE(A1,RIGHT(TRIM(A1),1),""))))

  • now that we have the first and last non-whitespace positions, we can extract the trimmed string with MID(A1, first, last-first+1)

Community
  • 1
  • 1
Mick O'Hea
  • 1,619
  • 2
  • 14
  • 20
  • Wow, all this just to eliminate leading and trailing whitespace. What a pain! Well, thanks for the contribution. – Shavais Jan 11 '18 at 18:12
  • Good formula. Who would want to write it, but once written this was an easy drop-in solution. – Mike Jun 28 '19 at 13:38