Trim Trailing Spaces Only

[Quintios]

I have text imported into Excel. I would like to run through the text and remove the trailing spaces only. There will definitely be spaces at the beginning of each cell and I need to keep those.

For example:

<space><space><space>This is the text<space><space><space>

needs to become:

<space><space><space>This is the text

As such, the trim function will not work. I searched on this site but didn't see any posts that related to this particular example (or I'm a noob and the post went over my head.)

Any and all help appreciated!

Onwards,

Q-

 

[bryanbayfield]

Is there not a [tt]RTRIM[/tt] function?

 

[Quintios]

Should have specified, I'm using Excel 97. RTRIM might be a part of XP? Either way, I don't have a RTRIM function.

Did someone write a RTRIM function?

Onwards,

Q-

 

[bryanbayfield]

Strange, it's in Excel 2000, could've sworn it was in the previous version.

I think we may be talking at cross-purposes here - I meant VBA Excel, rather than Excel.

You can use a compund function to do it in Excel, assuming the cell you want to trim is A1

[tt]=LEFT(A1,FIND(TRIM(A1),A1)+LEN(TRIM(A1))-1)[/tt]

 

[TonyJollans]

Hi Quintios,

There is no way (or certainly no easy way) to do this in a formula and a UDF is the way to go. Bryan was right to suggest RTrim (which is in '97 VBA) for this purpose.

Bryan,

I haven't checked your formula in detail (because I'm sure it's right as far as it goes) but it will fail if there are multiple internal spaces in the string as these get stripped down to single spaces by Trim and the Find won't (if you see what I mean).

Enjoy,
Tony

 

[bryanbayfield]

Well, you learn something new every day. I didn't realise that it trimmed multiple embedded spaces - that doesn't sound very clever - in fact, it's complete and utter **###@@@$$!

I don't think there's an easy way to do it without VBA, sadly.