Find cells with a space at the end

  • Thread starter Thread starter Alain Dekker
  • Start date Start date
A

Alain Dekker

How do I find all cells (with text in it) that ends with a space ie.
"This is some text "
which I either want to highlight or automatically fix to:
"This is some text"

I'm using Excel 97. I have tried to turn on showing special character like
you can in Word (where a space appears as a dot), but couldn't find that
command. The Find command also doesn't help.

Many thanks,
Alain
 
How do I find all cells (with text in it) that ends with a space ie.
"This is some text "
which I either want to highlight or automatically fix to:
"This is some text"

I'm using Excel 97. I have tried to turn on showing special character like
you can in Word (where a space appears as a dot), but couldn't find that
command. The Find command also doesn't help.

Many thanks,
Alain
Click to expand...

The trailing space may be the normal <space> character, but, especially if the
source of this data was an html document (or downloaded from the web), might be
a <nbsp> (char(160).

To determine if the last character is a <space>, something like:

=ISNUMBER(FIND(" ",A1,LEN(A1)))

To determine if the last character is a <nbsp>, something like:

=ISNUMBER(FIND(CHAR(160),A1,LEN(A1)))


To remove the last character:

=IF(ISNUMBER(FIND(" ",A1,LEN(A1))),LEFT(A1,LEN(A1)-1),A1)
or
=IF(ISNUMBER(FIND(CHAR(160),A1,LEN(A1))),LEFT(A1,LEN(A1)-1),A1)
--ron
 
Try this small macro:

Sub blankremover()
For Each r In ActiveSheet.UsedRange
v = r.Value
If Right(v, 1) = " " Then
r.Value = Left(v, Len(v) - 1)
End If
Next
End Sub
 
Thanks for all those answers!

Gary''s Student said:
Try this small macro:

Sub blankremover()
For Each r In ActiveSheet.UsedRange
v = r.Value
If Right(v, 1) = " " Then
r.Value = Left(v, Len(v) - 1)
End If
Next
End Sub
Click to expand...
 
Back
Top