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
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

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,958
Office Version
  1. 365
  2. 2010
Platform
  1. 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:
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,699
Messages
5,833,204
Members
430,196
Latest member
rez5656

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top