Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Removing ANSI Char (2000)

    I need to break down some data in an excel workbook. Multiple Data has been entered into one cell using the "Alt" + "Return" keys to separate the data.
    Is this the ANSI Bel character? How is it best to separate this data. Is there some combination of a "Clean" or "Substitute" formula with a "text to columns" set-up. Is there a quick fix?

    Many thanks
    Rob

  2. #2
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing ANSI Char (2000)

    Thanks - I have now solved this
    Alt + Return is ANSI Character 10 (Found in a previous post) - I can never remember all these ! - Is there a list of these somewhere in Excel help !?

    Therefore I can enter a formula
    =SUBSTITUTE(A1,CHAR(10),"]")

    Then I can use Excel's text to columns feature to separate, using the "]" symbol

    Thanks
    Rob

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Removing ANSI Char (2000)

    Hi Rob,
    You could also go straight to the Text to Columns feature, click in the Other text box and enter Alt+0010 (on the number pad).
    HTH.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing ANSI Char (2000)

    It's ASCII actually, not ANSI (that's a standard's organization) and from Excel's Help, just enter "ASCII" and there are several references. One of them points to this on Microsoft's site http://office.microsoft.com/en-us/assistan...1331361033.aspx

    The order of the ASCII char set determines how sorting is done too (is "a" > "A" sort of answers, or is "1" > ":" sort of things). It's been around since the 1960's (and there are other character set tables like EBCIDIC from IBM but this one is the dominant one).

    Deb

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

    Re: Removing ANSI Char (2000)

    I don't think that any of those tables are going to help with what the original poster was asking. If you look at those tables, you would be loking for "|" (that is the closest to what displays in the cell if you don't allow the text to wrap), and from the table that would be character 124 not 10.
    Legare Coleman

  6. #6
    Star Lounger
    Join Date
    Mar 2004
    Location
    Essex, England
    Posts
    93
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing ASCI Char (2000)

    Thanks for all your help.

    I have now realised that if you use the ' =code() ' formula in excel you can find out exactly which non-printing ASCI character you are dealing with. Legare's tip of entering alt + ASCI code straight into the Text to Columns feature does exactly as I need.

    However, Jujaraf's link to the ASCI tables has still been useful though (especially the table of non-printing characters). For example the tables help you to identify that ASCI 10 is the 'carriage return'. It helps to explain which characters I'm dealing with and to understand the reason they are there in the first place.

    Thanks again

    Robert

Posting Permissions

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