[Solved] Changing from scientific notation to normal

[Arjani]

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?

[FJCC]

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.

[Arjani]

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.

[acknak]

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

[Villeroy]

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).

[acknak]

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"?

[MrProgrammer]

Hi, and welcome to the forum.

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.

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.

[acknak]

..."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!

[Arjani]

I got it to work with Villeroy's tips, thanks everyone!

[MrProgrammer]

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

9000000000000000000E-18
9000000000000000000E-18
1000000000000000000E-17
1400000000000000000E-17

[acknak]

Interesting! Thanks for the follow-up.

[Villeroy]

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