| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Delete space at beginning | Excel Discussion (Misc queries) | |||
| delete extra space | Excel Worksheet Functions | |||
| Delete space | Excel Discussion (Misc queries) | |||
| Is there a way to use the space bar to delete? | Excel Worksheet Functions | |||
| Is there a way to use the space bar to delete? | Excel Worksheet Functions | |||
Linear Mode
