+ Reply to Thread
Results 1 to 7 of 7

Number format to Display 20 character long number.

  1. #1
    Registered User
    Join Date
    06-03-2013
    Location
    Matamoros, Mexico
    MS-Off Ver
    Excel 2007
    Posts
    52

    Question Number format to Display 20 character long number.

    Hello everyone!!!

    I received a request from a coworker regarding custom formatting some numbers in his spreadsheet. Those numbers are serial numbers of 20 characters long. Sometimes in my files I use this custom number formatting ###0 and its enough for the data I handle. But when I tried to use it in his spreadsheet, the following shows:

    8456891070060510000

    The cell must look like this: 08456891070060510302

    The reason to have it like this is due to a Delivery Program requirement to deliver Set-top Units for repair. The Delivery Program do not recognize other format than the above. My coworker takes the data from a spreadsheet, and the spreadsheet needs a custom number format to display the correct number.

    Can anyone help me to find a custom number format to be able to display as my coworker need it??
    We will really appreciate the favor.

    Best regards and please have a great day.

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Number format to Display 20 character long number.

    To preserve all characters you need to format cell as text and then paste or enter long numbers.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Number format to Display 20 character long number.

    Excel can only store numbers up to 15 digits (see any of the good internet articles on "double precision" variables in computer programming languages). In order to store a number of 20 digits in Excel, that number must be stored as a text string and not a number. You and your coworker will need to get into the habit of entering an apostrophe before hand entered numbers ('12345678901234567890) or otherwise forcing the numbers to be text strings.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    06-03-2013
    Location
    Matamoros, Mexico
    MS-Off Ver
    Excel 2007
    Posts
    52

    Re: Number format to Display 20 character long number.

    Hello Alkey & MrShorty...

    We tried Alkey's idea and it worked, but only if the spreadsheet is new and the numbers are being entered in the spreadsheet. But our spreadsheet is generated from another source. The idea of the apostrophe will not apply in our case.

    What advise can you give us if the data is already in a spreadsheet??

    Best regards!!

  5. #5
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Number format to Display 20 character long number.

    If the data is already in the workbook and stored as numbers, all digits beyond 15 are permanently, irretrievably lost.
    Entia non sunt multiplicanda sine necessitate

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Number format to Display 20 character long number.

    What advise can you give us if the data is already in a spreadsheet??
    Not much. If the data is already in the spreadsheet as numbers, the 5 digits are already lost. This kind of thing has got to be taken care of when the data is imported/entered into the spreadsheet. If Excel is importing from a text file, you need to instruct Excel to treat the data as text and not number. If the data is brought into the spreadsheet from a macro or program, then the macro or program needs to be programmed to put the data in Excel as text.

    Can you better explain how the data are being entered/imported into Excel?

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Number format to Display 20 character long number.

    Format all the cells as text then paste the values into the worksheet using Paste Special, Match Destination Formatting. This will eliminate the need for extra characters to force text formatting. There are not many choices when it comes to very long numbers.

    Whatever you do, the correction can't be done after importation as the last digits are forever gone.

    All previous answers are correct.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

+ 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. Highlight duplicate rows based off 9 first characters of my 12 character long number
    By girl_alex in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 04-18-2014, 06:55 PM
  2. getting long format number in normal xlsx sheet
    By sateeshkumarj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-19-2014, 07:42 AM
  3. [SOLVED] How to create a formula/format for a long number to include a period and not round
    By Rita Bush in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-13-2013, 01:13 PM
  4. Replies: 3
    Last Post: 01-31-2006, 04:30 AM
  5. Replies: 1
    Last Post: 09-12-2005, 01:05 PM

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