LibreOffice Calc: How to convert unformatted text

Question

How do I paste unformatted text (csv or tab delimited for instance) into LibreOffice Calc and then format it as numbers (without editing every cell by hand). When I paste the text below it puts a ' in front of everything so if I try to convert it to a number it's still text.

Text 0.01% 3% 5.1%
Asdf 0.2% 4% 6.0%

So for example if I paste special that as unformatted text with space delimiters and then convert 0.01% to a number (which would then say 0.0001 if it did what I wanted), it now says '0.01% in the cell.

Answer 1

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.

Answer 2

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.