Delete Space
 

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

Delete Space
 

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).

--
Regards,
Tom Ogilvy



"wagz" wrote:

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

Delete Space
 

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

=CODE(RIGHT(A1,1))

a "real" space will show 32
--
Gary''s Student - gsnu200740


"wagz" wrote:

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

Delete Space
 

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.

--
n00b lookn for a handout


"wagz" wrote:

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

Delete Space
 

it returns a value of 160

"Gary''s Student" wrote:

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

=CODE(RIGHT(A1,1))

a "real" space will show 32
--
Gary''s Student - gsnu200740


"wagz" wrote:

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

Delete Space
 

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.

"Tom Ogilvy" wrote:

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).

--
Regards,
Tom Ogilvy



"wagz" wrote:

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

Delete Space
 

On Wed, 29 Aug 2007 09:28:02 -0700, wagz
wrote:

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

Delete Space
 

Perfect, thanks!

"Ron Rosenfeld" wrote:

On Wed, 29 Aug 2007 09:28:02 -0700, wagz
wrote:

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

Delete Space
 

On Wed, 29 Aug 2007 12:28:02 -0700, wagz
wrote:

Perfect, thanks!

"Ron Rosenfeld" wrote:

On Wed, 29 Aug 2007 09:28:02 -0700, wagz
wrote:

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



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

Delete Space
 

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

--
Regards,
Tom Ogilvy


"wagz" wrote:

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.

"Tom Ogilvy" wrote:

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).

--
Regards,
Tom Ogilvy



"wagz" wrote:

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