Formatting a large number

[pagrender]

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

 

[VoG]

Try formatting the cell as Text before entering the number.

 

[pagrender]

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]

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]

Thanks for the info!

 

[Scott Huish]

With the initial value entered as text, here is a shorter formula:

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