Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    May 2001
    Location
    Philadelphia, Pennsylvania
    Posts
    676
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Invisible characters (Excel 2003)

    Even if I use something like ASAP's character removal utilities, it seems I cannot get rid of certain "things" in a cell that, for instance, make lookups or other functions fail because a number in the cell has something around it. I wind up doing a text-to-columns overwrite of the existing data as the only way to clean up the problem. Is there any utility out there that can truly show me actual cell contents? In having to do a text-to-column overwrite, what am I doing that changes the cell contents that some of these character removal utilities do not do? TYIA

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Invisible characters (Excel 2003)

    If I remember rightly for ASAP, these hidden characters are char 13 and char 10. Just as an experiment have you tried this:

    Create a new column and assume some of this data is in A2

    =SUBSTITUTE(SUBSTITUTE(A2,CHAR(10),""),CHAR(13),"" )
    Jerry

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Invisible characters (Excel 2003)

    ...and then I find this...could this work?

    Excel addin for ASAP
    Jerry

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Invisible characters (Excel 2003)

    What "things" are in the cell? It is tough to answer how they get there unless you provide more details...

    Thew most common are tabs (char9), LineFeeds (char10) and Carriage Returns (char13), though if imported some will also use a "sticky-space" (char160) to ensure that the item remains "text". Many programs, excel included, will ignore a space and convert text-numbers into real numbers even if the source has them as text.

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invisible characters (Excel 2003)

    Tell us what you want to leave in the cell, and a quick macro should eliminate everything else. If these are really "numbers", then you probably want to leave numeric digits and +, -, and decimal point.
    Legare Coleman

Posting Permissions

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