WE GOT A HIT!!!! I learned yet another thing... yes using number pad
allows data entry into FIND of ASCII Char() .

with number pad, entering 160 produces character that looks like an "a"
which my DOS book says is char(160) . Seems logical.

At first, when I entered 0160 into replace, I thought it didn't work.
then I noted that the was a blank space in the find field of the find
and replace dialog box. I then hit "replace all" and immediate all the
mysterious spaces were gone.

THANKS

windsurferLA wrote:
Thanks for super fast response by approach didnt work. I forgot to say
Im running XL97.

The replace or find would not accept any keyboard entries while the
alt-key way displayed. The machine beeps. I cant enter 0160 while the
alt key is depressed. Perhaps the ability to accept ASCII codes is a
feature added after XL97. Your approach seems to seek to enter the
ASCII code for a special symbol. I had been thinking along similar
lines. I tried €œ^s€� to mimic the code one uses for paragraph €œ^p€� and
tab €œ^t€� but it also did not work. . The CLEAN() function also did
not work, but now I understand that CLEAN() does not work on ASCII 160.

I note also that if I copy the content of the cell into Notepad, there
appears to be a space before and after the number. If I then copy the
string back from Notepad into Excel, Excel still does not find the
€œspace.€� I would think that Notepad would show something other than a
space if it was some special ASCII code.

I have been reading
http://www.mvps.org/dmcritchie/excel...tm#debugformat
suggested by another.
It suggested using CODE() to find out the ASCII. When I used it, it
returned €œ160.€� Thus you are probably right on the character being the
html non-breaking space. Another suggestion to press F2 and then ENTER
does not seem to change the nature of the special character.

At this point, the issue has become an intellectual challenge. My
immediate problem was solved by my manually editing the 45 individual
numbers. However, exploring the problem has sensitized me to potential
issues.

HOWEVER, ANY OTHER IDEAS SHORT OF UPGRADING TO A NEWER VERSION OF XL
WILL BE EXPLORED???


Ron Coderre wrote:
Perhaps the "spaces" are really html non-breaking spaces.

Try something like this:

From the Excel main menu:
<edit<replace
Find what: Hold down the [Alt] key....type 0160...release the [Alt] key
Replace with: (leave this blank)
Click the [Replace All] button

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"windsurferLA" wrote:

I'm seeking to analyze data copied from stock broker on-line
statement. The values copy across as text with (what appears to be)
a space before and after each number. I can manually delete the
spaces by pressing F2 and the delete or backspace key as appropriate
for the space at the front and rear of the number which is otherwise
of the form
-$123,456.00

The function Value() does not retrieve the value from the text stream.

The function Trim() does not remove the spaces.

The menu function, "replace" does not find the spaces when you use
the space bar to enter the item to be searched for.

Is there a special character that appears as a space but is not a space?