Parse scientific notation percent

[cptwunderlich1]

Hi all,
I want to copy and analyze some data generated by an external tool.
Two columns contain percentages, e.g., “1.4%” which is correctly parsed and displayed by LibreOffice (i.e. as the number 0.014 and displayed as 1.4%).
However, smaller values are generated in scientific notation, e.g., 9.1e-4%, and are only recognized as text.

By hand, I would have to remove the ‘%’, divide by 100 and then format as percent (only removing the ‘%’ and formatting as percent would turn “9.1e-4%” into 0,091%, instead of the correct 0,00091%).

Is there any (semi-)automatic way to achieve this?

Here a few sample values to copy and test:

3.5e-2%
7.3e-2%
0.10%
0.30%
0.20%
7.5e-2%
8.7e-3%

(LibreOffice Calc 6.4.7.2 on Ubuntu 20.04.4 LTS)

[erAck]

(questioning whether such scientific notation percentage value is really useful, but anyway…). 9.1e-4% could be forced to formula expression with =9.1e-4% which is interpreted as =(9.1e-4)% and results in 0.00091% as expected.

[cptwunderlich1]

Thank’s for the response! As I said, that format is generated by an external tool, so nothing I can do about that. However, I filter the rows using Calc, e.g., A < 0.01, but evaluates to false for the text rows, filtering out the wrong rows! That’s why I need them as numbers, even if it displays as “0.00%”.

Your solution works, but I guess I would have to manually add that to every cell.

[erAck]

Mark the cell range, hit Find&Replace (Ctrl+H)

• Find: .+
• Replace: =$0
• Other options:
  • Current selection only
  • Regular expressions

Hit Replace All.

[Villeroy]

When imported with English locale setting and “detect special numbers” option, percent and scientific values import correctly, the combined formats don’t. The following formula fixes this:

=IF(ISNUMBER(A1);A1;NUMBERVALUE(LEFT(A1;LEN(A1)-1);".")/100)

[sokol92]

Or

=--SUBSTITUTE(A1;".";MID(3/2;2;1))

[cptwunderlich1]

Thank you very much! This is brilliant!

[cptwunderlich1]

@ sokol92 Hm, this doesn’t work for me for the scientific notation values ("#VALUE!").

[sokol92]

Yes, you are right, I’m sorry. This “universal formula” for your case works correctly in Excel and does not work in Calc.