CODEWITHSUNDEEP
libreoffice
CrazyCasta
CrazyCasta

Reputation: 28362

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

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.

Upvotes: 9

Views: 18212

Answers (3)

Lulu
Lulu

Reputation: 1

I insert a column with the function CNUM() and reference to the text value. Then copy special paste: only numbers. Then supress the old column.

Upvotes: 0

m3asmi
m3asmi

Reputation: 1262

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.

Upvotes: 19

pdw
pdw

Reputation: 8861

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.

Upvotes: 12

Related Questions