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

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

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

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

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

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

On Wed, 29 Aug 2007 09:28:02 -0700, wagz <(E-Mail Removed)>
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

Perfect, thanks!

"Ron Rosenfeld" wrote:

> On Wed, 29 Aug 2007 09:28:02 -0700, wagz <(E-Mail Removed)>
> 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
>

On Wed, 29 Aug 2007 12:28:02 -0700, wagz <(E-Mail Removed)>
wrote:

>Perfect, thanks!
>
>"Ron Rosenfeld" wrote:
>
>> On Wed, 29 Aug 2007 09:28:02 -0700, wagz <(E-Mail Removed)>
>> 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

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