Find cells with a space at the end

[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

 

[Ron Rosenfeld]

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

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

 

[Eduardo]

Hi,
in an adjacent column do

=trim(a1)

being a1 the cell where you have the text

 

[Gary''s Student]

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

 

[Alain Dekker]

Thanks for all those answers!

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