Ask Your Question

Number in scientific notation instead of EAN (GTIN) code

asked 2017-05-03 15:37:59 +0200

MrGeminus gravatar image

updated 2017-05-03 22:14:24 +0200

Lupp gravatar image

Hi guys,

I got I csv file with EAN-s. The problem is that the EAN-s are displayed in calc as scientific, example 3,53E+12. Is there a possibility to change it back to normal formatting?


edit retag flag offensive close merge delete


What are EAN-s? Using obscure acronyms is a stupid idea when you ask a technical question.

gabix gravatar imagegabix ( 2017-05-03 16:03:44 +0200 )edit

I trough everybody knows that. EAN is short from European Article Number. It consists of 13 digits.

MrGeminus gravatar imageMrGeminus ( 2017-05-03 16:17:22 +0200 )edit

And somebody, perhaps, thinks that everybody knows that EAN is short for enteral artificial nutrition, or for Egyptian Association of Neonatology, or for EPIET Alumni Network, etc. Acronym Attic returns 43 meanings of EAN, for your reference.

For future, consider asking better questions.

gabix gravatar imagegabix ( 2017-05-03 16:24:10 +0200 )edit

Hello MrGeminus, to format a number value in Calc you could do this:

1) select the cell that contains the number
2) right-click in the cell and choose the menu option "Format Cells..."
3) in the listbox "Category" select the item named "Number",
4) in the textbox called "Format code" type "#" ( without quotes ), and click OK
librebel gravatar imagelibrebel ( 2017-05-03 16:28:33 +0200 )edit

I have given an example, how can 3,53E+12 be connected to Egyptian Association of Neonatology for example? But anyway, you want to judge my question or to help to solve the problem? If you need more info, which I didn't mention, you can always ask.

MrGeminus gravatar imageMrGeminus ( 2017-05-03 16:33:04 +0200 )edit

2) for quick access to cell properties, simply hit Ctrl+1.

gabix gravatar imagegabix ( 2017-05-03 16:33:15 +0200 )edit

Hi Librebel,

Tried it right now but it shows me 3528700000000 for all articles and the EAN can't be the same for all of them. But anyway, thanks bro!

MrGeminus gravatar imageMrGeminus ( 2017-05-03 16:40:31 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-05-03 22:06:32 +0200

Lupp gravatar image

updated 2017-05-03 22:11:50 +0200

Concerning the OriginalQuestion:
Obviously in the import dialog the respective column was not properly set to 'Text' instead of 'Standard' to accept the EAN (GTIN actually) as text. What already wrongly was named a number (and should be named a code or something alike) was actually "recognised" as a number - and that's really bad.

However, as long as the un-number isn't more digits and has a fixed length or is assured to have no leading zeros you will view it again as soon as you make the column(s) wide enough.

Nonetheless you shouldn't miss to convert the un-numbers explicitly to text: 'Data'>'Text to Column...' is the appropriate tool if you want to do that in situ. By a formula in a different column you will get the text consisting of decimal digits by =TEXT(A2;"0000000000000") - 13 zeros as format code - e.g. if the un-number is placed in cell A2.

edit flag offensive delete link more


Hi man,

The first thing I tried was to make the column wider, but this doesn't worked out. The EAN remained in this stupid format. I also tried to change the format to general or to number, but this doesn't worked neater. I tried to find the solution on the internet, but failed. Because of that I wrote this question here. Regarding the EAN itself, it can have sometimes leading zeros, but in this case not. And it should have always 13 digits. I will try this with 13 zeros. Thanks!

MrGeminus gravatar imageMrGeminus ( 2017-05-06 10:34:01 +0200 )edit

I commence to disbelieve that your "scientific EAN" still reside in cells of type 'Number'. Did you try? You may use the =ISNUMBER(CellReference) function in an empty cell for testing. If you did this referencing one of your EAN cells and got TRUE, there must be a serious problem with your system/install of LibO. The 'General' number format behaved as I described (displaying 13-digit numbers as exact integers in wide cells e.g.) for decades now.

Lupp gravatar imageLupp ( 2017-05-06 11:28:24 +0200 )edit

What exactly do you see if you enter the EAN cell for editing?
What's your document's CV? Was it saved to an alien file format once?
You may consider to cautiously reset your user profile as described here. It might be corrupted.
Please also tell your OS(+Version) and your LibO version.

Lupp gravatar imageLupp ( 2017-05-06 11:35:34 +0200 )edit

answered 2017-05-03 16:27:36 +0200

gabix gravatar image

updated 2017-05-03 17:00:03 +0200

For this case, when you import a file, define Text as the column type for the column containing those EANs in the text import dialog.

Attached are two files: a CSV sample (I had to change the filename extension to .ods because .csv is not accepted here) and a screenshot of the import dialog with the settings that result in correct import.

ean_sample.png ean_sample.ods

edit flag offensive delete link more


Tried, nothing changed.

MrGeminus gravatar imageMrGeminus ( 2017-05-03 16:47:29 +0200 )edit

Could you share a sample file? Just one row of data containing any such EAN would be enough.

gabix gravatar imagegabix ( 2017-05-03 16:50:21 +0200 )edit

I can't share these information with third parties. Sorry.

MrGeminus gravatar imageMrGeminus ( 2017-05-03 16:57:55 +0200 )edit

Then see my answer, edited.

gabix gravatar imagegabix ( 2017-05-03 17:01:53 +0200 )edit

Yeah, I have done that the first time, I selected in the drop down many column type the option text, but it doesn't change nothing. But thanks.

MrGeminus gravatar imageMrGeminus ( 2017-05-03 17:09:40 +0200 )edit

Then still, try to share a file. No real information needed, just the structure. Replace actual data with any garbage.

gabix gravatar imagegabix ( 2017-05-03 17:20:10 +0200 )edit

What I would need to probably find advice: An example csv that caused the problem for the Original Questioner (OQ).

Lupp gravatar imageLupp ( 2017-05-03 21:49:25 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-05-03 15:37:59 +0200

Seen: 366 times

Last updated: May 03 '17