+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Ronald Dodge
    Guest

    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. #2
    Harlan Grove
    Guest

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

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


  3. #3
    JE McGimpsey
    Guest

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

    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 <erJE8OwAFHA.2932@TK2MSFTNGP10.phx.gbl>,
    "Ronald Dodge" <ronald.dodge@cfgraphics.com> 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. #4
    Harlan Grove
    Guest

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

    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. #5
    JE McGimpsey
    Guest

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

    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 <1106677714.978933.14200@f14g2000cwb.googlegroups.com>,
    "Harlan Grove" <hrlngrv@aol.com> 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. #6
    Ronald Dodge
    Guest

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

    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" <hrlngrv@aol.com> wrote in message
    news:1106677714.978933.14200@f14g2000cwb.googlegroups.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. #7
    Harlan Grove
    Guest

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

    "Ronald Dodge" <ronald.dodge@cfgraphics.com> 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1