Delete Space

[Guest]

I have a column of data and each one of these email address in this column
has a space at the end of it. I have used the "Clean" and "Trim" functions
to eliminate the space at the end. I am at whits end with this one. I have
even done a find and replace to find " " with "". Nothing seems to work.
Any help would be appreciated.

Thanks

 

[Guest]

Maybe it is a non-breaking space character:

for each cell in selection
cell.value = Replace(cell.value,chr(160),"")
Next

If you need to test what it is

Sub TestCell()
Dim s as String
s= Right(ActiveCell.Value,1))
msgbox "->" & s & "<-, is chr: " & asc(Right(ActiveCell.Value,1))
End sub

or in an adjacent cell
=code(Right(A1,1))


Whatever it says use as the argument to chr above (if it appears to be the
character you want to replace).

 

[Guest]

May not be a "real" space. In another cell enter:

=CODE(RIGHT(A1,1))

a "real" space will show 32

 

[Guest]

This is kind of crude, but if you copy and paste the column to notepad then
import it back to your sheet as "space" delimited I think you'll get what you
want.

 

[Guest]

it returns a value of 160

May not be a "real" space. In another cell enter:

=CODE(RIGHT(A1,1))

a "real" space will show 32

 

[Guest]

So is the first part of this reply code for VB or what do I do with it? The
code search came back with 160.

 

[Ron Rosenfeld]

I have a column of data and each one of these email address in this column
has a space at the end of it. I have used the "Clean" and "Trim" functions
to eliminate the space at the end. I am at whits end with this one. I have
even done a find and replace to find " " with "". Nothing seems to work.
Any help would be appreciated.

Thanks

Select your range, then select
Edit/Replace

Find what: --hold down the <alt> key while you type 0160 on the numeric keypad
(do not type the numbers on the number keys above the keyboard). Then release
the <alt> key.

Replace with: --leave blank--




--ron

 

[Guest]

Perfect, thanks!

Select your range, then select
Edit/Replace

Find what: --hold down the <alt> key while you type 0160 on the numeric keypad
(do not type the numbers on the number keys above the keyboard). Then release
the <alt> key.

Replace with: --leave blank--




--ron

 

[Ron Rosenfeld]

Perfect, thanks!

You're welcome. Thanks for the feedback.
--ron

 

[Guest]

when you post in programming, often the answer you get is VB code. You
should specify when you want otherwise.