Need Excel Formula - Removing trailing spaces that doesn't use clean or trim

[wadezone]

I am doing some data comparison/testing and need a way to remove trailing spaces from the end of each cell. The number of spaces at the end could vary depending on the data. Clean/TRIM is removing spaces in the middle that I must retain. I have also trying using SUBSTITUTE to replace but again is removing data in the middle of the text. Any ideas?

Thanks in advance

 

[RoryA]

Welcome to the forum.

Is VBA acceptable?

 

[mopp1]

CSE formula (Ctrl-Shift-Enter)
=MID(A1,1,MAX((MID(A1,ROW(INDIRECT("1:"& LEN(A1))),1)<>" ")*ROW(INDIRECT("1:"& LEN(A1)))))

 

[wadezone]

I prefer where I could use the excel formula and maybe append it to some other string manipulation I already have in my template. I then compare the source and target using exact. What do you have in VBA? I figured you are going to loop through the string starting from the right until I get an alphanumeric value. I get the impression that this may not be possible by excel formula. I thought there was a LTRIM or RTRIM but no luck.

 

[wadezone]

Oh cool mopp1, let me try that. I am not familiar with INDIRECT. I will go check it out.

 

[RoryA]

VBA does have RTrim, so it would simply be something like:

Function RTRIM(sIn as String) as String
RTRIM = VBA.RTrim$(sIn)
End Function

You can use that in a formula just like you would use TRIM, as long as you don't need to pass multiple-cell ranges or arrays to it.

 

[wadezone]

Good point Rory, thanks for the reminder. I am curious what INDIRECT will do to see if it works even though the VBA function is cleaner. I will let you both know how it goes in a bit. Thank you again.

 

[RoryA]

Just bear in mind that INDIRECT is volatile so any formula that uses it (or depends on a cell that does) will recalculate whenever the workbook does, even if its input cells haven't actually changed. If you use it a lot, you will probably notice a slowdown.

 

[mopp1]

You can get rid of the volatile INDIRECT function (which would be recommended if you have lots of data) by using the following CSE formula instead. Replace 99 with whatever will bound the max number of characters in your data items.

=MID(A1,1,MAX((MID(A1,ROW($1:$99),1)<>" ")*(MID(A1,ROW($1:$99),1)<>"")*ROW($1:$99)))

 

[Rick Rothstein]

Here is a normally entered, non-volatile formula for you to consider...

=LEFT(A1,FIND("|",SUBSTITUTE(A1,RIGHT(TRIM(A1)),"|",LEN(A1)-LEN(SUBSTITUTE(A1,RIGHT(TRIM(A1)),"")))))

And, if you don't mind array-entered** formulas, here are two more non-volatile formulas that should work...

=LEFT(A1,MAX((MID(A1&REPT(" ",99),ROW($1:$99),1)<>" ")*ROW($1:$99)))

=LEFT(A1,MAX((MID(A1,ROW($1:$99),1)=RIGHT(TRIM(A1),1))*ROW($1:$99)))

**Commit the last two formulas using CTRL+SHIFT+ENTER and not just Enter by itself