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)
42,63
27,78
14,72

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.

Comparing text lists with conditional formatting in OOo/LO Calc

· Given that I hadn't found enough completely satisfying information myself, I had to make a separate blog post about it, as an acquaintance asked me for how to solve list issues.
· I deem my explanation better than others', as I think this one explains the relationships much better (:smug:).
· Applies to both OpenOffice.org Calc and LibreOffice Calc.

This post actually assumes the following:
• That you already have two lists of textual information that you want to compare;
• That you've already created your own custom styles for formatting the necessary cells, but that it's hard to get one's head around about how to do all that comparing.

So, then:

• Select the column range that you want to format (for example A1:A15);
• From the menu bar choose Format > Conditional Formatting;
• In its window, "Condition 1" is selected by default; below choose "Formula is", and in the next field it gets important,

  where inside COUNTIF brackets you specify the target column (range array) you want to compare the selected column against:

  So, you have A1:A15 selected (which eventually gets formatted) and you want to compare it against B1:B15;

  the latter you have to write in array format, with a dollar $ sign before every column letter and row number in the range ($B$1:$B$15;  );

  After the semicolon, specify the last cell of the selected range (the one you want to format, not the target range), which is A15. The whole formula line goes almost exactly like this:
  
  COUNTIF($B$1:$B$15;A15)=1

=1 means that you apply the format only if there is a match;
  =0 is inverse: most cells are formatted, those that match are not.
  
• Don't forget to specify your custom formatting style for cells, then click OK.

All this should work in older OO.o versions, too.

Source: Conditional Formatting: Comparing two Lists (has screenshots), archived from www.openofficetips.com/2006/02/01/conditional-formatting-comparing-two-lists/