[Solved] Changing from scientific notation to normal

Discuss the spreadsheet application

[Solved] Changing from scientific notation to normal

Postby Arjani » Wed Jan 16, 2013 7:19 am

I have a long list of numbers that I pasted into Calc that looks like this:

9.000000000000000000e+00
9.000000000000000000e+00
1.000000000000000000e+01
1.400000000000000000e+01

etc.

To make it easier to manipulate these numbers, I'd like to change them to 'normal' numbers, so my list would look like this:

9
9
10
14

etc.

Is there a way to do that in calc?
Last edited by Hagar Delest on Thu Jan 17, 2013 9:22 am, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 3.1 on Windows 7
Arjani
 
Posts: 3
Joined: Wed Jan 16, 2013 7:07 am

Re: Changing from scientific notation to normal

Postby FJCC » Wed Jan 16, 2013 7:49 am

If those really came in as numbers then you should be able to do this. Highlight all of the cells you want to change and go to the menu Format -> Cells. On the Numbers tab set the category to Number and choose the format General.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7790
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Changing from scientific notation to normal

Postby Arjani » Wed Jan 16, 2013 9:30 pm

Thanks for the tip, but it didn't work. Anyone know of a way to do it? I can't do it manually since there are thousands of numbers. The actual numbers that I pasted into it are like this:

9.000000000000000000e+00
9.000000000000000000e+00
1.000000000000000000e+01
1.400000000000000000e+01

I tried changing a few of those e's to E's, since that seems to be how Calc writes scientific numbers, and then tried the above tip, but that doesn't work either.
OpenOffice 3.1 on Windows 7
Arjani
 
Posts: 3
Joined: Wed Jan 16, 2013 7:07 am

Re: Changing from scientific notation to normal

Postby acknak » Wed Jan 16, 2013 9:47 pm

Try this:
1) Select the column
2) Format > Cells > Numbers > Category: Number; Format: General ... OK
3) Data > Text to Columns; Separator: Tab ... OK

After that, the values are numbers; you can change the format to whatever you want:
Format > Cells > Numbers > Category: Number; Format: 0
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Changing from scientific notation to normal

Postby Villeroy » Thu Jan 17, 2013 12:19 am

acknak wrote:1) Select the column
2) Format > Cells > Numbers > Category: Number; Format: General ... OK
3) Data > Text to Columns; Separator: Tab ... OK

If this does not work:
1) Select the column
2) Format > Cells > Numbers > Category: Number; Locale: English(any); Format: General ... OK
3) Edit > Find&Replace...
[More Options]
[X] Current Selection Only
[X] Regular Expressions
Search: .+ (a point and a plus)
Replace: &
[Replace All]

The number format locale takes the point into account if your normal decimal separator is comma.
The find&replace effectively re-enters all data into the new locale context (this seems to fail with text2columns).
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28674
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Changing from scientific notation to normal

Postby acknak » Thu Jan 17, 2013 4:14 am

Are numbers in scientific notation locale specific?

Ok, trying it now, I see that they are. Good grief. I would think that scientific notation should be universal--well, there ought to be some number format that works everywhere--but that's probably too much to hope for. Yeesh.

Who was it that said "integers are the only real numbers"? ;-)
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Changing from scientific notation to normal

Postby MrProgrammer » Thu Jan 17, 2013 4:48 am

Hi, and welcome to the forum.

Arjani wrote:Thanks for the tip, but it didn't work. Anyone know of a way to do it?
The OP's "numbers" are almost certainly text, and will be displayed in black with View > Value Highlighting. For if they were numbers they would be displayed with the three significant digits by default (9.00E+000), yet the OP is seeing 19 siginificant digits. Since the cells are text, changing the number format cannot affect those cells. But Text to Columns should work, as long as this text matches the convention for scientific notation in your locale. If not, attach a document demonstrating the difficulty (use Post Reply, not Quick Reply, and don't attach a picture instead of the document itself) and be sure to indicate your locale. To display your locale see option (OpenOffice.org > Preferences on a Mac, Tools > Options on other platforms) Language Settings > Languages > Locale Setting. Help > Index > format codes;numbers should show examples of scientific notation for your locale.

acknak wrote:Who was it that said "integers are the only real numbers"? ;-)
"God made the integers; all else is the work of man."
-- Leopold Kronecker (1886)

If this answered your question 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.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3936
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Changing from scientific notation to normal

Postby acknak » Thu Jan 17, 2013 5:11 am

MrProgrammer wrote:..."God made the integers; all else is the work of man."
-- Leopold Kronecker (1886)

That's exactly the one I was thinking of. Thanks, MrP!
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: Changing from scientific notation to normal

Postby Arjani » Thu Jan 17, 2013 6:58 am

I got it to work with Villeroy's tips, thanks everyone!
OpenOffice 3.1 on Windows 7
Arjani
 
Posts: 3
Joined: Wed Jan 16, 2013 7:07 am

Re: [Solved] Changing from scientific notation to normal

Postby MrProgrammer » Mon Feb 11, 2013 10:54 pm

acknak wrote:there ought to be some number format that works everywhere
I realize the topic is solved, but some of the volunteers may be interested in additional information I found. From Open Document Format for Office Applications (OpenDocument) Version 1.2 Part 2: Recalculated Formula (OpenFormula) Format OASIS Standard:
    Regardless of the current locale, an evaluator shall accept numbers matching this regular expression (which does not include a decimal point character) and convert it into a Number. If the value ends in %, it shall divide the number by 100:
    [+-]? [0-9]+([eE][+-]?[0-9]+)?)%?

So, the OP should be able to remove the decimal separators, adjust the exponent (admittedly a niusance), and use the locale-independent forms:
Code: Select all   Expand viewCollapse view
9000000000000000000E-18
9000000000000000000E-18
1000000000000000000E-17
1400000000000000000E-17
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3936
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: [Solved] Changing from scientific notation to normal

Postby acknak » Mon Feb 11, 2013 11:38 pm

Interesting! Thanks for the follow-up.
AOO4/LO5 • Linux • Fedora 23
User avatar
acknak
Moderator
 
Posts: 22756
Joined: Mon Oct 08, 2007 1:25 am
Location: USA:NJ:E3

Re: [Solved] Changing from scientific notation to normal

Postby Villeroy » Tue Feb 12, 2013 12:23 am

Why not import numbers in the first place? Choose English import locale and "special numbers" option.

The result looks like this in a German document:
9,00E+000
9,00E+000
1,00E+001
1,40E+001

These are true numbers. menu:Format>Default Formatting resets the formatting to plain integer:
9
9
10
14
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 28674
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: Google [Bot], Majestic-12 [Bot] and 35 guests