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

On Wed, 3 Feb 2010 12:02:17 -0000, "Alain Dekker"
wrote:

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,L EN(A1)-1),A1)
--ron

Hi,
in an adjacent column do

=trim(a1)

being a1 the cell where you have the text

"Alain Dekker" wrote:

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


.

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

--
Gary''s Student - gsnu201001


"Alain Dekker" wrote:

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


.

Thanks for all those answers!

"Gary''s Student" wrote in message
...
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

--
Gary''s Student - gsnu201001


"Alain Dekker" wrote:

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


.