On 9 Oct 2006 16:53:41 -0700, wrote:

James Silverton wrote:
Hello, daddylonglegs!
You wrote on Mon, 9 Oct 2006 15:31:02 -0700:
=CLEAN(A1)

I tried that and it does not work.

Right. The problem I encounter is when I cut-and-paste a column of
numbers from a posting that I am reading using Google Groups, the
"numbers" are treated as text, and nothing seems to undo that. The
reason: Google Groups has replaced some of the space and tab
characters in the original posting (I have looked at the "original"
source) -- notably the first space or tab -- with char(160), which is a
"non-breaking space" in the ANSI character set.

FYI, one work-around is to cut-and-paste from the "original" source
(click Show Options). Users of other newsreaders might not have this
problem, as long as the non-breaking space character is not in the
"original" source. (It never has been in the postings that I have
seen.)

Another work-around is to cut from the formatted posting (not the
"original" source), paste into a file created using Notepad, then use
Import External Data to bring into Excel.

CLEAN removes non-printable characters according to HELP

Right. And apparently the "non-breaking space" is considered a
printable character, as arguably perhaps it should be just as its
cousin, the normal space character, is.

But this might have nothing to do with the OP's problem since in my
experience, Google Groups always has more than one leading "space",
which is a mix of normal and non-printing space characters.

You can use Regular Expressions to deal with that problem. Although you can
easily implement it in VBA, if the strings are less than 256 characters long,
the simplest method is to download Longre's free morefunc.xll addin from:
http://xcell05.free.fr/

And then use the formula:

=REGEX.MID(A1,"\S.*\b") which will remove all leading and trailing
"white-space" characters, including <tab, <space, <nbsp etc.

If the problem is to convert the value to a real number, then you can use:

=--REGEX.MID(A20,"-?(\d+(\.\d*)?|\.\d+)")

which should extract integer or floating point positive or negative numbers.




--ron