Results 1 to 4 of 4
  1. #1
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Odd data (Excel 2003)

    I have been given some data dumped from an Oracle database.
    Some of it is odd, and I don't understand what is going on.

    The numbers in the attached spreadsheet appear to have trailing spaces,

    But if you remove one trailing space, then press enter the last digit disappears.
    Put the cursor anywhere in the last 6 digits, and the cursor seems to sit in the middle of a number rather than between numbers. But this does not happen with the first 6 digits.
    Regards
    John



  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Odd data (Excel 2003)

    The cells in the spreadsheet you attached contain two six-digit numbers separated by a carriage return (ASCII character 13). You can extract the first number using

    =VALUE(LEFT(A1,6))

    and the second number using

    =VALUE(RIGHT(A1,6))

    These formulas can be filled down as far as needed.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Odd data (Excel 2003)

    Thanks Hans

    Yes the data has come by joining different records in the database, but I would never have worked out that there was an ASCII character 13 between them.
    Regards
    John



  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Odd data (Excel 2003)

    This formula in B1 will tell you:

    =CODE(MID(A1,7,1))

    It can be filled down if needed.

Posting Permissions

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