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.

This problem is still in the latest version of LibreOffice. It is a ridiculously hard problem to try to work around. Anyone know how to make a bug report? — EoghanM, Mar 12 '15 at 19:15

m3asmi · Answer 1 · 2019-12-21T13:10:11.847

19

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.

edited Dec 21 '19 at 13:10

answered Sep 10 '14 at 11:40

m3asmi

1,252
1
16
36

Very interesting. I have no idea what you mean by "back reference". Could you explain how this works? It seems to not mess with cells where I put a ' in the cell (which is good, because I only want it to mess with the autogenerated cells). – CrazyCasta Sep 11 '14 at 04:56
list of regular expressions https://help.libreoffice.org/Common/List_of_Regular_Expressions – m3asmi Sep 11 '14 at 14:12
3

I do understand regexp, but I don't understand how that regexp isn't matching the single quote. Nothing that I read on that page seems to explain what's special about the single quote that makes it not get matched. For instance, if I fed "s/^.*$/$0/g" into sed I would expect to get back exactly what I gave it. None of this should take away from the fact that your solution works very nicely, I just want to understand **why** it works. – CrazyCasta Sep 11 '14 at 21:00
1

@CrazyCasta I think the leading single quote is not actually part of the cell value. Try a simple search for ' and you will get no matches. – suriv Sep 30 '14 at 16:11
If Calc has already imported them as text, this only replaces the text with the same text and does nothing. For this to work, the cells have to be formatted to numbers first (Ctrl-Shift-1). The regex is in fact replacing cell value with itself, not removing a quote. – Marko Kohtala Apr 08 '22 at 11:20

score 12 · Accepted Answer · answered Apr 24 '13 at 18:49

12

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.

answered Apr 24 '13 at 18:49

pdw

8,359
2
29
41

Awesome, thanks, as soon as this 6 hour timer runs out you've got the bounty. Can't believe I never noticed that :P – CrazyCasta Apr 25 '13 at 11:58
1

Ok but how do you invoke this on data that is already in the spreadsheet; e.g. I have a column of data in the format '$3.5 mill.', which Libreoffice doesn't interpret. how do I get calc to check again for numbers? – AnjoMan Nov 04 '14 at 18:21

score 0 · Answer 3 · edited Dec 20 '20 at 21:04

0

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.

edited Dec 20 '20 at 21:04

Pierre.Vriens

2,117
75
29
42

answered Dec 20 '20 at 14:49

Lulu

1

you probably need to edit the second half to make it clear in English. – Izana Dec 20 '20 at 19:07

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

3 Answers3

Linked