Trim function doesn't clean out ASCII Code 160 (Space)
 

I have imported some data from the web site. It's using the character code
of 160 for the space rather than the character code of 32. I attempted to
use the TRIM function to clean this space, but it's not cleaning it out as
the LEN function returns the same number of characters with the TRIM
function as without the TRIM function. The data has this character code of
160 at the end of the numeric value that is techincally formatted as text
from when imported by the website, which I tried using the VALUE function,
but cause of this space, it returns the value error due to unrecognized
format.

Is there any other way to address this issue via formula writing without
necessarly having to take extra steps such as the Text to Column wizard or
creating a custom function within VBA? I can create the function, but I
rather not have to.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

=--SUBSTITUTE(x,CHAR(160),"")

There's a sub already written by David McRitchie:

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

For a formula:

=TRIM(SUBSTITUTE(A41,CHAR(160)," "))



In article ,
"Ronald Dodge" wrote:

Is there any other way to address this issue via formula writing without
necessarly having to take extra steps such as the Text to Column wizard or
creating a custom function within VBA? I can create the function, but I
rather not have to.

JE McGimpsey wrote...
....
For a formula:

=TRIM(SUBSTITUTE(A41,CHAR(160)," "))
....

The TRIM is unnecessary if the ultimate goal is numeric conversion.
Excel will ignore any number of leading and/or trailing ASCII spaces
[CHAR(32)] when such strings are used as arithmetic operands. E.g.,
=(REPT(" ",32)&"123"&REPT(" ",20))+1

True.

I was confused by the OP's "techincally (sic) formatted as text". On
rereading it's clear that the result should be numeric.

In article . com,
"Harlan Grove" wrote:

The TRIM is unnecessary if the ultimate goal is numeric conversion.
Excel will ignore any number of leading and/or trailing ASCII spaces
[CHAR(32)] when such strings are used as arithmetic operands. E.g.,
=(REPT(" ",32)&"123"&REPT(" ",20))+1

Yes, Excel does with regards to ASCII code 32, but it doesn't with regards
to ASCII code 160. The SUBSTITUTE function is what did the trick to get
around the ASCII code of 160.

Give it a try yourself. Both ASCII codes are spaces, but they aren't the
same as far as converting text to numbers within Excel is concerned. When I
initially started to check it out, I thought it was the standard space
character, but then when I applied the CODE function to the last character
of the imported text, it returned the code of 160, not the expected code of
32.s

Now why the webmaster had used the ASCII code of 160 instead of the standard
ASCII code of 32, I have no idea. That's just how the information was
imported into my Excel file from the web page.

--
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

"Harlan Grove" wrote in message
ups.com...
JE McGimpsey wrote...
...
For a formula:

=TRIM(SUBSTITUTE(A41,CHAR(160)," "))
...

The TRIM is unnecessary if the ultimate goal is numeric conversion.
Excel will ignore any number of leading and/or trailing ASCII spaces
[CHAR(32)] when such strings are used as arithmetic operands. E.g.,
=(REPT(" ",32)&"123"&REPT(" ",20))+1

"Ronald Dodge" wrote...
....
Now why the webmaster had used the ASCII code of 160 instead of the
standard ASCII code of 32, I have no idea. That's just how the
information was imported into my Excel file from the web page.
....

Char code 32 (ASCII spaces) need not be represented as-is in HTML, but char
code 160 (nonbreaking spaces) must be reproduced wherever they appear in
HTML documents. In HTML tables, nonbreaking spaces provide alignment.