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

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

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),""),"")

=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.

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
```

I used this function in a worksheet formula like =RTrim2(A2)
ASKER

Wow. This all seems very complex. Would simply TRIM(B5) work?

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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

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