Ask Me Help Desk

Ask Me Help Desk (https://www.askmehelpdesk.com/forum.php)
-   Spreadsheets (https://www.askmehelpdesk.com/forumdisplay.php?f=395)
-   -   Excel 15 digit issue, tried converting to text, text to column feature negates fix (https://www.askmehelpdesk.com/showthread.php?t=66616)

  • Feb 26, 2007, 12:46 PM
    kaiotes
    Excel 15 digit issue, tried converting to text, text to column feature negates fix
    I have the following numbers that exceed 15 characters that needs to be split into its own columns.
    Down the road, there would be thousands of such rows of data with the first couple set of unique numbers.

    890432453253208820,5004500558,05CC,1,0,0,0,0,0,0, 0000,5.0000,2007-01-11 00:00:00.000,2007-02-10 23:59:59.000,ttt,

    890432253253208820,5004600558,05CC,1,0,0,0,0,0,0, 0000,5.0000,2007-01-11 00:00:00.000,2007-02-10 23:59:59.000,ttt,

    890432453253208822,5004500758,05CC,1,0,0,0,0,0,0, 0000,5.0000,2007-01-11 00:00:00.000,2007-02-10 23:59:59.000,ttt,

    Obviously one method would be to use the replace command, but I can not due to the uncertainty of the pattern of the first set of number.

    I have tried formating them as text after pasting it into excel, it does the trick, but I need each set of numbers that are separated by commas into its own column, but once I do text to column, the first set of numbers which exceed 15 digits no longer show the full digits, only the first 15 digits.

    Any help would be greatly appreciated.
  • Feb 26, 2007, 01:16 PM
    cajalat
    The 15 digit of precision is not new for Excel. Microsoft claims that they are adhering to strict IEEE 754 which basically limits to 15 digits the precision of a real number which is how numbers are represented in Excel. Anything larger than that is represented with a maximum of 15 digits using E+## notation.

    What you can do is import those numbers as TEXT. I took the liberty of cutting/pasting what you listed in a .CSV file and I did a data import and changed the properties of the first column to be TEXT before the import. That worked. I'm wondering if that's what you wanted to do vs. having to manually change each row.

    Also, there is a 3rd party tool that you can give a try which might help you. The free version has annoying pop-up dialogue boxes that remind you that this is a free program etc etc. The paid version doesn't. See if this solves your problem:

    xlPrecision

    Casey
  • Feb 26, 2007, 02:24 PM
    kaiotes
    When I attempted to import the data (all in a txt file) into a new sheet, I still have the problem of 15 digits, I had the first column already as text.

    Can you provide more detailed instructions on the import process?
    Thanks again
  • Feb 26, 2007, 03:40 PM
    cajalat
    Here's what I did:

    1. I started with a blank spreadsheet.
    2. From the menu: DATA --> Import External Data --> Import Data
    -- Find and choose the CSV file (you'll need to change File of types to Text Files

    Now a Text Import Wizard comes up

    Step 1 of 3
    - Change to Delimited, click Next

    Step 2 of 3
    - Uncheck Tab and check Comma, click Next

    Step 3 of 3
    - Click TEXT (this will change the first column from General to Text)
    - Click FINISH

    Next Screen (Import Data) just click OK (basically you're choosing the default to import to the existing worksheet)

    That worked for me and the first column shows the entire number in the first column.
  • Aug 30, 2010, 07:54 AM
    radhikakhtau
    Save it as a .csv file and open in MS Access... all the data will be converted into text... then export to excel
    If this does not work open the .csv file (using open with by right clicking on the .cvs file and select open with) in Word Pad... delete the " (inverted comma) notation from the beginning and end of each row... and save the file and open in MS Access and once your data appears in the desired format... export in excel
  • Aug 30, 2010, 07:54 AM
    radhikakhtau
    Save it as a .csv file and open in MS Access... all the data will be converted into text... then export to excel
    If this does not work open the .csv file (using open with by right clicking on the .cvs file and select open with) in Word Pad... delete the " (inverted comma) notation from the beginning and end of each row... and save the file and open in MS Access and once your data appears in the desired format... export in excel
  • May 6, 2013, 02:20 AM
    qudratpakhtoon
    How we can convert text or number to password shape, I mean when I enter text in a cell so I want to appear in password shape same like this.*******

  • All times are GMT -7. The time now is 03:49 PM.