LibreOffice Calc: How to convert unformatted text to numbers (or other format)

This procedure is tested in LibreOffice 4.0.2. Paste as unformatted text, and choose the following options:

• Select English as language, or some other language that uses periods as decimal separator.
• Select "Detect special numbers".

Now the percentages will be pasted as numbers instead of text, and you can use the format options to display them the way you like.

To replace cells with leading single quote: ' (U+0027)

Press CTRL+H » Click More options » Check Regular expressions.

Search for ^.*$ and replace with &

That's kinda RegEx engine hack. It searches for everything before the cell start and replaces it with the existing cell content - but without the single quote. Have a look at the back reference if you want to read more.