Remove trailing spaces without losing middle spaces

[simoncr]

Hi
I have been trying, unsuccessfully, to remove trailing spaces from a set of numbers (all are 10 Characters) whilst keeping any spaces that may exist in the middle.
TRIM does not work because some of the numbers have 2 spaces in the middle and the TRIM function removes one of these.
I would be very grateful if someone could provide a solution.
Thanks in advance.

example of number set below:
[TABLE="width: 99"]
<tbody>[TR]
[TD]03685 [/TD]
[/TR]
[TR]
[TD]040710 [/TD]
[/TR]
[TR]
[TD]089284632 [/TD]
[/TR]
[TR]
[TD]0893221 [/TD]
[/TR]
[TR]
[TD]0893870004[/TD]
[/TR]
[TR]
[TD]089947002 [/TD]
[/TR]
[TR]
[TD]03685 12[/TD]
[/TR]
[TR]
[TD]03685 14[/TD]
[/TR]
[TR]
[TD]0124 5[/TD]
[/TR]
[TR]
[TD]0124 6[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[Joe4]

Welcome to the Board!

Excel VBA has RTRIM, which only removes spaces from the end. However, there is no native Excel function that does that.
Here is a link that comes up with a formula that will do that (see "The formula for RTRIM"): http://eforexcel.com/wp/article-18-ltrim-and-rtrim-through-excel-formulas/

 

[pbornemeier]

I can't think of a formula, but if a UDF is OK, this post: http://www.mrexcel.com/archive/Formulas/26078.html has the answer:

Posted by mseyf on August 08, 2001 11:47 AM

Martin:

unfortunately, RTrim (and LTrim) are VBA commands, but if you're not adverse to using a UDF, you could try something like:

Function uTrim(cell)
uTrim = RTrim(cell.Value)
uTrim = LTrim(uTrim)
End Function

you can copy this into a VBA module and use the utrim() command in your sheet.

HTH

Mark

 

[MARK858]

I thought Application.Trim doesn't remove internal spaces but WorkSheetFunction.Trim does, if so is this not better rather than using both LTrim and RTrim?

 

[simoncr]

Thank you!
I am not a user of VBA so the excel formula you provided is perfect!

 

[Joe4]

You are welcome!
Glad we could help.