Eliminate blanks at the end of a description

  • Thread starter Thread starter lehigh
  • Start date Start date
L

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
 
=+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.
 
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!
 
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.
 
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
 
Back
Top