+ Reply to Thread
Results 1 to 5 of 5

Last digits in long numbers changed when opening file

  1. #1
    Registered User
    Join Date
    06-08-2017
    Location
    NJ, USA
    MS-Off Ver
    2016
    Posts
    13

    Last digits in long numbers changed when opening file

    Hello - currently working on a project where I import data from an .xlsx file then run some macros to massage the data, and ultimately load to a sql table.

    This morning I noticed that one of the longer numeric fields, which contain 19-25 digits, is being modified and I'm not sure how to get around it.

    The column containing the number is in GENERAL format when I open the file (so it displays in scientific notation), and I change it to NUMBER in the macro with
    Columns("Q").NumberFormat = "0"

    When I look at the source file in NOTEPAD ++ it shows "6585341937000906783" but in my spreadsheet, and when I use that cell to feed into my variable, it's changed to "6585341937000900000" (the last 4 digits are 0's, always)

    How can I keep the value of the numbers when it seems to be modified when the file is opened?

    Thanks for any tips/advice!

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Last digits in long numbers changed when opening file

    in the past excel only support 14 digits , hence the zeros - I have not checked on latest 365/2016 version but suspect its the same
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Last digits in long numbers changed when opening file

    I think it might be 15 digits, but either way is is not as many as you need. The safest option is to import the numbers as text, then you can have many more digits

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,684

    Re: Last digits in long numbers changed when opening file

    but the calculations i think will still only uses the 15 digits , so when you convert the text to a number
    others may have a workaround
    http://access-excel.tips/excel-maxim...ts-workaround/

  5. #5
    Registered User
    Join Date
    06-08-2017
    Location
    NJ, USA
    MS-Off Ver
    2016
    Posts
    13

    Re: Last digits in long numbers changed when opening file

    Thanks ... I actually don't have to do any calculations on the numbers - but they do serve as a key in the db. I think my approach is going to be to import the data via data connection, not open the file/copy paste. Then as davsth said, I can import as text and my problem should be solved.

    Appreciate it!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Opening a .txt file with 10 unknown digits on the end of the file name
    By Seth_ in forum Excel Programming / VBA / Macros
    Replies: 26
    Last Post: 06-28-2018, 04:06 AM
  2. [SOLVED] Opening CSV file through VBA takes unrealistically long
    By JasperD in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 11-15-2016, 09:54 AM
  3. [SOLVED] Combine digits in double digits in 4 numbers without repeating each other
    By jorel in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-14-2014, 08:18 AM
  4. Replies: 0
    Last Post: 05-17-2013, 09:21 AM
  5. Replies: 20
    Last Post: 09-29-2012, 08:45 PM
  6. [SOLVED] Does a file know how long it was edited or changed (Excel)
    By Lauralalulu in forum Excel General
    Replies: 1
    Last Post: 03-03-2006, 03:10 PM
  7. HELP - File Names are being changed to numbers
    By Nik in forum Excel General
    Replies: 1
    Last Post: 01-12-2006, 10:30 AM

Tags for this Thread

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