Excel query

Gers

I want to input numbers such as 17099131256910302 - it shows variously as:

9131256910302.00 when formatted as a number to 0 decimal places

1.70913E+15 when formatted as general or text


How can I get this number to show correctly please?

Lorian

right-click cells chose "format cells" and select "number"

TrickyDicky101

Format the cell as Text - Excel has a 15 digit numeric precision limitation so you won't be able to input numbers as true numeric values beyond this. Be aware that this does mean the value entered is a textual representation of a number, however.

bengalknights

You could put do '1234567890000 as the number

Gers

Lorian wrote: »

right-click cells chose "format cells" and select "number"

It's already in as a number!

TrickyDicky101 wrote: »

Format the cell as Text - Excel has a 15 digit numeric precision limitation so you won't be able to input numbers as true numeric values beyond this. Be aware that this does mean the value entered is a textual representation of a number, however.

Now, the 15 digit limit makes sense, thanks!

bengalknights wrote: »

You could put do '1234567890000 as the number

It's in as a number!

MarkFromCornwall

Gers wrote: »

I want to input numbers such as 17099131256910302 - it shows ...

1.70913E+15 when formatted as general or text

That's worrying. It should be 1.70991E+16

Heedtheadvice

Quite right Mark!


I also wonder why, Gers, you want such high precision. What are you trying to achieve or was that just an example?


If it is just that you wish to display it more or less correctly (up to the 15 digit precision) then format the cell as Custom (there should be examples under that format style in the menu for it) and type the format as 0 i.e just a row of zeros. That will give your 17 digits with no leading zeros and the last two characters with be rounded off to the 15 digit precision showing as 00 rather than 02.


If you wanted to show leading zeros then increase the custom number of zeros to those desired. Other examples shown in the list such as #,##0.000

Gers

Heedtheadvice wrote: »

Quite right Mark!


I also wonder why, Gers, you want such high precision. What are you trying to achieve or was that just an example?

It's the number of a certificate.

170913125691 0302 Wee space between 1 and 0 shows it correctly
1709131256910300 Number - changes the final digit to 0
1.70913E+15 Text No idea what this is!

Carrot007

Gers wrote: »

It's in as a number!

Exactly what was meant. The prefixing ' makes it text. I have a macro to add such to the current column for situations like this.

Carrot007

Gers wrote: »

It's the number of a certificate.

So 100% no need to be a number and should be text.


Numbers are number so you can calculate on them. This is just a string whihc happens to be numerical and excel tries to double guess what you want and gets it wrong.

JP1978

Adding ' before the number as above will fix it - just like when you try and enter a phone number 01782123456, excel will format it as 1782123456 - by using the ' before the 0 it will display as intended.