[Solved] Number Format Problem

Discuss the spreadsheet application

[Solved] Number Format Problem

Postby johnoo » Sun Apr 25, 2021 2:48 pm

Am trying to put the number 20127019035143 into a cell, but it comes out as : -2.01270E+013.
I have selected Numbers in Format, what am I doing wrong?
Last edited by Hagar Delest on Sun Apr 25, 2021 3:00 pm, edited 1 time in total.
Reason: tagged solved.
Best Regards
Johnoo

Windows 10 AOO 4.1.10
johnoo
 
Posts: 476
Joined: Tue Jan 01, 2008 7:58 pm

Re: Number Format Problem

Postby Hagar Delest » Sun Apr 25, 2021 2:54 pm

Enlarge the column?
Else, try to set the number of decimal places to 0 to force the number format.
LibreOffice 7.0.5 on Xubuntu 21.04 and 7.0.5 portable on Windows 10
User avatar
Hagar Delest
Moderator
 
Posts: 29749
Joined: Sun Oct 07, 2007 9:07 pm
Location: France

Re: Number Format Problem

Postby johnoo » Sun Apr 25, 2021 2:57 pm

Great! Enlarged column now OK, thanks
Best Regards
Johnoo

Windows 10 AOO 4.1.10
johnoo
 
Posts: 476
Joined: Tue Jan 01, 2008 7:58 pm

Re: [Solved] Number Format Problem

Postby John_Ha » Sun Apr 25, 2021 3:50 pm

johnoo wrote:Am trying to put the number 20127019035143 into a cell

Be aware that 14 digits is very close to the limit of integers supported by Calc.

Also, neither integers nor floating point numbers can have a resolution of more than about 15 or 16 digits. Any digits over 15 or 16 are ignored or set to zero and do not take part in calculations.

Calc suggests you can display 20 digits but that is wrong.
Last edited by John_Ha on Sun Apr 25, 2021 3:58 pm, edited 1 time in total.
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 8379
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: [Solved] Number Format Problem

Postby johnoo » Sun Apr 25, 2021 3:55 pm

John_Ha wrote:
johnoo wrote:Am trying to put the number 20127019035143 into a cell

Be aware that 14 digits is very close to the limit of integers supported by Calc.

Also, neither integers nor floating point numbers can have a resolution of more than about 15 digits. Any digits over 15 are ignored or set to zero and do not take part in calculations.

Calc suggests you can display 20 digits but that is wrong.


Thanks, I wanted a 14 digit number, just happens to be my Library card number!
Best Regards
Johnoo

Windows 10 AOO 4.1.10
johnoo
 
Posts: 476
Joined: Tue Jan 01, 2008 7:58 pm

Re: [Solved] Number Format Problem

Postby John_Ha » Sun Apr 25, 2021 4:00 pm

johnoo wrote:Thanks, I wanted a 14 digit number, just happens to be my Library card number!

Pedantically, it is a label and not a number to be calculated, so should really be stored as a 14 character string and not as a number. :super:

See Solved] Import 20-digit ICCID numbers from CSV
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 8379
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: [Solved] Number Format Problem

Postby johnoo » Sun Apr 25, 2021 4:05 pm

John_Ha wrote:
johnoo wrote:Thanks, I wanted a 14 digit number, just happens to be my Library card number!

Pedantically, it is a label and not a number to be calculated, so should really be stored as a 14 character string and not as a number. :super:


So, how in Calc, do i store it as a 14 character string?
Do I have to change a setting in Format?
Best Regards
Johnoo

Windows 10 AOO 4.1.10
johnoo
 
Posts: 476
Joined: Tue Jan 01, 2008 7:58 pm

Re: [Solved] Number Format Problem

Postby John_Ha » Sun Apr 25, 2021 4:11 pm

johnoo wrote:So, how in Calc, do i store it as a 14 character string?
Do I have to change a setting in Format?

Format > Cell > Text. Note that by default text strings are left justified and numbers are right justified.
Attachments
Clipboard01.png
Clipboard01.png (5.1 KiB) Viewed 1057 times
LO 6.4.4.2, Windows 10 Home 64 bit

See the Writer Guide, the Writer FAQ, the Writer Tutorials and Writer for students.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 8379
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: [Solved] Number Format Problem

Postby johnoo » Sun Apr 25, 2021 4:25 pm

John Thanks, will remember in future (I hope)!
Best Regards
Johnoo

Windows 10 AOO 4.1.10
johnoo
 
Posts: 476
Joined: Tue Jan 01, 2008 7:58 pm

Re: [Solved] Number Format Problem

Postby RoryOF » Sun Apr 25, 2021 4:48 pm

John_Ha wrote:
johnoo wrote:Thanks, I wanted a 14 digit number, just happens to be my Library card number!

Pedantically, it is a label and not a number to be calculated, so should really be stored as a 14 character string and not as a number. :super:

See Solved] Import 20-digit ICCID numbers from CSV


Also, looking like numbers, but actually labels, are zip codes, credit card numbers, phone numbers, bank account numbers - one does not (in general) do calculations using these.
Apache OpenOffice 4.1.10 on Xubuntu 20.04.2 (mostly 64 bit version) and very infrequently on Win2K/XP
User avatar
RoryOF
Moderator
 
Posts: 32539
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland


Return to Calc

Who is online

Users browsing this forum: No registered users and 16 guests