Link to home
Start Free TrialLog in
Avatar of Software Engineer
Software Engineer

asked on

Excel RTRIM

Hi:
Can Excel's "RTRIM" formula be used to remove trailing spaces on the right-hand side of a cell as in =RTRIM(B5) or do you have to use TRIM as in TRIM(B5)?
Thanks!
SE
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

If your version of Excel has a RTRIM function then you should use that. My Excel 2010 doesn't seem to have it.
LTrim for the cell C1:
=REPLACE(C1,1,FIND(LEFT(TRIM(C1),1),C1)-1,"")

RTrim for the cell A1:
=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),""),"")
I use the Beta Channel of Microsoft 365, which is the absolute most recent version released outside Microsoft. There is no RTRIM function in Excel's list of built-in functions. Neither is there one in Google Sheets.

VBA does have an RTrim function, however. You may use it to create an RTrim2 function for use in worksheet formulas bu putting the following code in a regular module sheet (not a worksheet code pane or ThisWorkbook). Save the file with .xlsm, .xlsb or .xls file extension, and make sure to enable macros when you first open the file.
Function RTrim2(text As String) As String
RTrim2 = RTrim(text)
End Function

Open in new window

I used this function in a worksheet formula like  =RTrim2(A2)
Avatar of Software Engineer
Software Engineer

ASKER

Wow.  This all seems very complex.  Would simply TRIM(B5) work?
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Distinguished Expert in Excel 2018
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2020
              Experts Exchange Top Expert VBA 2018 to 2020