Formatting a large number

pagrender

Well-known Member
Joined
Sep 3, 2008
Messages
652
Hello all,

I would like to take the following number:
27800400158000011

and convert it to:
27 800 400 158000 011

There's one issue though... when I enter this:
27800400158000011

Excel will change the last two numbers to zero - it looks like this:
27800400158000000

I've tried to change the custom formats to 00000000000000000 but it still changes my data. I've also used this formula:
=LEFT(A1,2)&" "&MID(A1,3,3)&" "&MID(A1,6,3)&" "&MID(A1,9,6)&" "&RIGHT(A1,3)

but the formula will not work until I can fix the fact that it changed 011 to 000 at the end of the string.

Does anyone have any suggestions?

Thanks,
Pete
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try formatting the cell as Text before entering the number.
 

pagrender

Well-known Member
Joined
Sep 3, 2008
Messages
652
I actually asked the question for someone else, who posed the question to me through email. Your reply works when I tested it - Thanks for the simple solution.

I'll ask him if it works with his data.

Do you know why Excel automatically changed the last few digits to zeros?
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Excel will only display a number to 15 significant digits. If you enter a longer number into a cell formatted as General or Number, Excel will 'truncate' it by substituting zeroes for the digits after digit 15.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,953
Office Version
365, 2010
Platform
Windows
With the initial value entered as text, here is a shorter formula:

=LEFT(A1,2) & " " & TEXT(RIGHT(A1,15),"000 000 000000 000")
 
Last edited:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,993
Messages
5,514,672
Members
409,014
Latest member
evenyougreg

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top