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

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.