Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Ronald Dodge
 
Posts: n/a
Default 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


  #2   Report Post  
Harlan Grove
 
Posts: n/a
Default

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

  #3   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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.

  #4   Report Post  
Harlan Grove
 
Posts: n/a
Default

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

  #5   Report Post  
JE McGimpsey
 
Posts: n/a
Default

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



  #6   Report Post  
Ronald Dodge
 
Posts: n/a
Default

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



  #7   Report Post  
Harlan Grove
 
Posts: n/a
Default

"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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lost my Paste Mike R Excel Discussion (Misc queries) 11 August 29th 05 12:22 AM
Trim function aehan Excel Worksheet Functions 3 January 25th 05 01:31 PM
How to use TRIM function Sky Warren Excel Worksheet Functions 3 January 8th 05 06:06 PM
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. Robert AS Excel Worksheet Functions 4 December 2nd 04 11:49 AM
How to resize a comment box, by embedding code into a function? JJ Excel Worksheet Functions 13 November 16th 04 09:44 PM


All times are GMT +1. The time now is 03:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"