Eliminate blanks at the end of a description

[lehigh]

Hi All

I copied about 300 desciptions in a column from another source.
They all have aprox. 10 to 20 blanks at the end ( to the right) of
each description.
How can I trim these back without removing spaces between words?


Thanks for any help you can give me.


Tom Snyder

 

[Dave R.]

=+IF(ISNUMBER(FIND(" ",A1)),LEFT(A1,FIND(" ",A1)-1),A1)

try this.. it works as long as there is at least 1 space at the end, and
there are no double spaces in the description you want to keep.

 

[Scott]

In the cell next to the first one enter this
formula: "=trim(<enter the cell you want to clean>)".

This removes all non-printable characters and spaces,
except in the middle of text.

Cheers!

 

[Dave R.]

If you have any with one blank space at the end after this formula is done,
you can use
=IF(RIGHT(A1,1)=" ",LEFT(A1,LEN(A1)-1),A1) to remove that final space.

 

[Gord Dibben]

Tom

If you want to do them all without a helper column and are willing/able to use
a macro.

Sub TRIM_EXTRA_SPACES()
Dim cell As Range
For Each cell In Selection
If (Not IsEmpty(cell)) And _
Not IsNumeric(cell.Value) And _
InStr(cell.Formula, "=") = 0 _
Then cell.Value = Application.Trim(cell.Value)
Next
End Sub

Gord Dibben Excel MVP