[Solved] Disable scientific notation for long numbers

[jazzparadigm]

Edit: This topic is locked to prevent new posts.

I have to enter a couple of large numbers into OpenOffice Calc that act as identifiers. OpenOffice takes these numbers, rounds them, and puts them in scientific format for me. I want to disable this behavior.

Searches on the web show people with this problem are handed 'solutions' that merely cheat the system and are not an actual solution. Adding an apostrophe in front of the number is not a fix, nor is formatting the cell as text.

Is it possible to achieve this in a correct way?

[Hagar Delest]

Hi and welcome to the forum!

Change the formatting of the cell by selecting the second format of the Numbers category (the one that displays a zero in the Format code field). Or just increase the column width.

Please add '[Solved]' at the beginning of your first post title (edit button) if your issue has been fixed.

[jazzparadigm]

Does not work. Selecting that format from the numbers category gets overridden after pasting. Setting the correct category after the paste means the damage has already been done and the number has been rounded. Also, increasing the column width does not work.

[acknak]

Searches on the web show people with this problem are handed 'solutions' that merely cheat the system and are not an actual solution. Adding an apostrophe in front of the number is not a fix, nor is formatting the cell as text.

Once you enter these ID numbers, does it make sense to do arithmetic on them? If not, these IDs are not numbers, ok?

In Calc, a cell holds either a number or text, so if these are not numbers, they are text, and you enter text either by entering an apostrophe at the start, or by formatting the cell as text before you enter the data.

[Villeroy]

Adding an apostrophe in front of the number is not a fix, nor is formatting the cell as text.

Is it possible to achieve this in a correct way?

A spreadsheet is not the correct solution anyway. Use a database to store row sets with type-safe IDs.

[kingfisher]

The software will adopt scientific format even if you type long numbers into the Input Line. There seems to be no solution if you want to use long numbers in a spreadsheet.

[jazzparadigm]

Searches on the web show people with this problem are handed 'solutions' that merely cheat the system and are not an actual solution. Adding an apostrophe in front of the number is not a fix, nor is formatting the cell as text.

Once you enter these ID numbers, does it make sense to do arithmetic on them? If not, these IDs are not numbers, ok?

In Calc, a cell holds either a number or text, so if these are not numbers, they are text, and you enter text either by entering an apostrophe at the start, or by formatting the cell as text before you enter the data.

I want to constrain the cell to integers for a bunch of different reasons. The problem is that I do not want my software to decide the amount of accuracy of my numbers; after pasting a long number, information is dropped by OpenOffice due to rounding. The fact that my spreadsheet decides what information is kept is mind boggling to me.

Judging from the responses, it seems that in fact OpenOffice is not apt at working with large numbers if a user wants to be in total control of his information, is that correct?

[acknak]

If you want to store a numeric value, you can't get more than about 15 digits with Calc.

If you want to be in complete control of the cell content, the data must be stored as text.

[JohnSUN-Pensioner]

I want to constrain the cell to integers...

Integer variables range from -32768 to 32767
Perhaps you meant "long integer"?
Long integer variables range from -2147483648 to 2147483647.

I do not want my software to decide the amount of accuracy of my numbers

Your office suite is trying to help you. But failed.

If you want to be in complete control of the cell content, the data must be stored as text.

+1

A spreadsheet is not the correct solution anyway. Use a database to store row sets with type-safe IDs.

+1

[kingfisher]

If you want to use numbers stored as text in a formula, you can incorporate the VALUE function in your formula.

[Villeroy]

If you want to use numbers stored as text in a formula, you can incorporate the VALUE function in your formula.

VALUE can not evaluate more than 15 significant digits without rounding. Each and every spreadsheet value is a double unless it is a text or error value.
http://en.wikipedia.org/wiki/Double_pre ... int_format
Part numbers, tax numbers, phone numbers, zip codes and all other numeric IDs are stored as text as best IT practice. A usual database such as HSQLDB shipped with this office suite, knows "Big Integers" as a data type representing up to 19 decimal digits without decimals.

[Nocturnaloner]

I need to enter SKU numbers in a CSV spreadsheet for import into a database. One of my SKUs is 01E1. Is there something about this string that is inherently viewed as a 'long number'? It reverts to scientific formatting no matter what I do, which destroys the information. I'm amazed that software is designed in such a way that it destroys the information that people are trying to save. Can we have it not do that?

[peterroots]

SKU of 01e1 - well yes it is 1 times 10 to the power of 1 (or 10 if you prefer). calc will obviously display this in scientific notation as that is how you entered it. Calc is not destroying information you entered but is taking the information you entered literally.

[peterroots]

http://user.services.openoffice.org/en/ ... 45#p216645

[Jason1979]

I've been having the same problem as the original post. Copying long SKU's into Open office

What I just did that worked:
Copy data into Notepad
Copy from Notepad
Paste Special in Openoffice
In the Text Import dialog box, click ON the example spreadsheet at the bottom
Then you can choose "TEXT" in the "Column Type" drop down box.
Cheers!

[nbbooks]

I know it's been THREE YEARS, but has anyone at Open Office finally fixed this nonsense???

I publish books. Books get ISBN numbers. ISBN-10 and ISBN-13. That means 10 characters or 13 characters. (Amazon is smart enough to add alphabetic characters to make their own ASINs).

ISBNs are a string of numeric characters. No arithmetic intended.

I have my spreadsheet. I pre-format a number of entire rows to be TEXT with the @.

I get my sales stats from Amazon, Nook,and others, and paste them in. And, OF COURSE, Open Office sees the need to convert any field with just numeric characters to NUMBER!

Of all the work I do all day long, data entry, PHP, JavaScript... NOTHING is more frustrating than OpenOffice changing my formatted cells from TEXT to NUMBER, just because I have a string of numeric characters, ISBN-10 or ISBN-13, into the field.

When is this nonsense going to stop?? Or, do I move on to Apple Numbers or Excel itself?

[MrProgrammer]

I know it's been THREE YEARS, but has anyone at Open Office finally fixed this nonsense???

There is nothing to fix. You can paste data into a spreadsheet as text, and this feature has been available for a couple of decades. I first started using Open Office in 2010, and it was available then. I never have any trouble. Simply use the correct procedure. Use Edit → Paste Special → Unformatted Text, and be sure to mark the field as Text in the Text Import dialog. Note that says "in the Text Import dialog" and not "in the Format Cells dialog". Read [Tutorial] Text to Columns, Q16/A16.

If you've used the wrong procedure and have stored 10-digit or 13-digit numbers instead of 10-character or 13-character text in the cells, you can fix that with Text to Columns by temporarily formatting the cells as 0000000000 or 0000000000000 and then running Text to Columns, marking the field type as Text. Read the tutorial.

Or, do I move on to Apple Numbers or Excel itself?

Go right ahead, but if you use the wrong procedure, you'll have the same trouble in those products. The difficulty is with the procedure, not with the software.

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[nbbooks]

As the bright poster posted above, "workarounds" are not "procedures".

And, it's not about ME storing data, it's about Mint.com, Amazon.com, et.al. delivering data,

You keep reading the Tut, but I have to read data files provided by others.

[RusselB]

Can you provide examples of these data files so we can see just exactly how Calc is working with the data that you have to work with?

[jrkrideau]

I pre-format a number of entire rows to be TEXT with the @.

What is the @ doing? I may be missing something but I have never heard of formatting a Calc cell with an @.

The normal way would be to have a Style set to text and use it or do a Format > Cell > etc. to format a cell selection to text -- done before the import or paste.

[Lupp]

@jrkrideau : The @ in this context should be the 'Format code' displayed in the bottom control in the 'Numbers' tab of the 'Format Cells' dialogue if the option 'Text' was selected.

Presenting this setting as a 'Numbers' format may be a main source of many misunderstandings as the 'CellObject.Type' attribute is 2 (meaning text) in this case, and also the formula recognition process is overridden. This is the reason for what I always name the 'Text' (@) settung under 'Numbers' a FAKE format. In fact it is an instruction to NOT APPLY recognition procedures when content is entered.

[jrkrideau]

Ah, thanks Lupp.

I imagine I must have noticed that but since I would never think to format text that way it never stayed in my mind. Somehow I have the vague feeling that @ did something in Lotus123 but no idea of what.

[thinman3]

@jrkrideau...

The @ sign was an indicator that what followed it was a function....much like $ indicating an absolute address.

[jrkrideau]

@jrkrideau...

The @ sign was an indicator that what followed it was a function....much like $ indicating an absolute address.

AHA, thanks. It's been many, many years since I used 123 but I thought it was something.

[nbbooks]

My use of the term "@" refers to:



I seriously don't know what more to do, based on this window. Should I have to go find a manual? NOT!

If I paste "2015/12/12" into a "TEXT" field, that doesn't mean I want the field changed to "DATE".

If I paste an "ISBN Number" "97814537676xx" (I changed the last 2 digits here to xx for privacy) into a "TEXT" field, that doesn't mean I want OO to change the field to "Number" with Scientific Notation. "ISBN Number" is a string with only numeric characters.

You can't look up an ISBN Number with an "E-12" on the end of it!

This forum software is so antique. Primitive in that I cannot "paste" the screen image in, like Facebook, I can't even upload it here! And I have to wait some unknown amount of time in between posts. How ADHD.

[RoryOF]

A screen image file can be uploaded, as can almost any other file. Use the upload tab below the submit button on PostReply or FullEditor windows. If you wish the screenshot displayed inline you can select that option.

[acknak]

Are you still using OO 3.x?

I'm not sure it makes a difference, as far as I remember, OO Calc has always treated entries in text-formatted cells as literal text, but there have been changes that could account for the behavior you're seeing.

At least for me, pasting any of your sample values, as text, into a text-formatted cell takes the value as literal text with no changes.

Copy/paste of a number or date (not text) from a Calc cell does ignore the text formatting.

[nbbooks]

I "paste" data into OO worksheets, fields, in either of three ways:
1- Copied rows/columns from .csv files opened with OO.
2. Copied rows/columns from .xls files opened with OO - usually from Amazon.com in e-mail.
3. Copied html tables from websites. If it is a Xcel-looking table.

I have been doing this since 2006, at least. Then with Office, now with OO. I have Numbers, being a Mac person, but the interface is cluttered.

I don't just look at my .csv or .xls/.ods files for the beauty of it. I process the files with PHP.

This whole situation has been aggravated recently, as I evolved from processing .csv files, to processing .xls files using PHPExcel. I had a much better chance of formats surviving a Save As .csv, than I now am having with keeping it as a full .xls. The advantage of .xls, is that I can have multiple worksheets, and thus, e.g. keep my various Amazon.com channels in one book, instead of several loose .csv files.

The documentation for PHPExcel on GitHub or even StackOverflow is user beware. On a previous attempt, I found no one on here who processes .xls/.ods files with PHP. Maybe because it was summer and everyone was on vacation.

I'm going to attach two screen caps from my .xls from Amazon, this morning. (Using the nonintuitive method of scrolling BELOW the Submit button to find the file upload interface).

You will notice that Amazon has formatted the fields differently that the default OO text format of '@'.

In the first screen shot, Amazon does not use the '@' in the field where they put in alphanumeric characters that we humans interpret as a "date". In the second screen shot, Amazon puts a "'" in the first character of the UPC/ISBN field, to preserve it as a text field. None of this is reliable when receiving or copy/pasting from other sources. e.g. Amazon product sales are sent to me as .xls files via e-mail. My Kindle sales are select/copy/paste from my Kindle online account webpage. <-No problems with Kindle stats!

Working around one failure, can cause other failures. Like last week when my ISP sent a IP route to help the building next door, and wiped out my building and 5 others.

What have you done with PHPExcel reading OO created .xls files?

I am now OO 4. I'll update that in my Profile.

[nbbooks]

This isn't a workaround, but does address the Excel date format conundrum when processing a .xls file with PHP and PHPExcel, where the cell is formatted [$-409]YYYY-MM-DD;@.

Since OO doesn't preserve the date cell value as human readable text in an .xls, I found out how PHP can convert it.

$EXCEL_DATE=42349.6189117708;
$UNIX_DATE = ($EXCEL_DATE - 25569) * 86400;
echo 'Excel gmdate test ' . gmdate("Y-m-d", $UNIX_DATE);

Excel gmdate test 2015-12-11

Works for me. (See screen cap above for the date on the Amazon .xls cell, and the cell format.)

http://stackoverflow.com/questions/1111 ... -php-excel <- didn't find it on the first Google! Explanation is enlightening, in regards to differences between *NIX and MS dates.

This technique does not address other instances of OO flipping a cell format from Text to number for UPC/ISBN or other date data entries, especially strings of numeric characters separated with '/'s.

[ssateneth]

I know I'm necroing a very old thread, but this is the first result I got from google when I searched this exact problem, and this thread did not provide the answer, however I was able to find the answer on my own.

Select the cells/column/row/page that you want to disable automatic scientific notation with.
Go to "Format" at the menu bar
Click "Cells..."
Go to the "Numbers" tab
Go to the "Category" scrollbox
Scroll all the way down and click "Text"
Click "OK"

Tada, your numbers will no longer be formatted/rounded/whatever and will stay exactly as you entered them.