Tuesday, April 7, 2015

Convert percentage values formatted as numbers into percentages in LibreOffice

The Problem:

Suppose that percentage values have been entered in LibreOffice this way:

(the decimal separator is a comma)

and the objective is to make sure that 42,63 and other cells display as 42,63% and act like percentages instead of general numbers or normal text.

Only that the values are treated as normal (General) numbers. The problem is bigger, if lots of cells are formatted like that, and the dataset's percentage values use decimals, as in 25,5 and so on.

When AutoInput is active (in Tools menu > Cell contents), then entering text with a percentage sign, such as 25,5% — automatically formats the cell and cell contents as a percentage, in which actual cell content is 0.255. But this works when entering data manually.

Adding a percentage sign to data in existing cells may or may not work: in some cases, when adding a percentage sign to 42,63, it will instead become 0,43 or 4263,00%.

The solution:

Select these cells, format them as text.

Use Find & Replace (Ctrl+H) to add a percentage sign:
* You need regexp functionality, so check [\/] Regular expressions;
* Check [\/] 'Current selection' only;
* The 'Search in' drop-down box must have 'Values' selected;
* The 'Search for' field must contain a dollar sign $ to signify end of paragraph;
* The 'Replace with' field must contain an ampersand and a percentage sign: &%
* Click [Replace All].

You're not done yet, as that only adds a percentage sign to the end of each value, but does not put them into percentage format.

Select all these cells, and cut or copy them.

* Then paste cut/copied cells as unformatted text. This opens the Text Import window, where:
* values are (•) Separated by [\/] Tab;
* Check [\/] Detect special numbers
* (To make sure that cells are positioned properly, then don't merge delimiters.)
* Press OK.
Now, each value should be formatted as a percentage, and the percentage sign toolbar button must also be active for these cells.

I don't know if there's any difference when in the Text Import window 'Quoted field as text' is checked while pasting unformatted text.

To keep the original dataset intact, then experiment by using a copy of the file, or at least paste into a new file or new sheet in the same file.

This might also work with OpenOffice.org that supports the 'Detect special numbers' function.

No comments: