# Formatting a large number

#### pagrender

##### Well-known Member
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
Try formatting the cell as Text before entering the number.

#### pagrender

##### Well-known Member
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
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.

#### pagrender

##### Well-known Member
Thanks for the info!

#### Scott Huish

##### MrExcel MVP
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:

Replies
4
Views
324
Replies
3
Views
220
Replies
0
Views
88
Replies
1
Views
562
Replies
5
Views
605

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.

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.

### Which adblocker are you using?

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

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