Bug 97258

Summary:	Crazy behaviour when pasting percentage numbers from text files
Product:	LibreOffice	Reporter:	leprechaun
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	RESOLVED WORKSFORME
Severity:	normal
Priority:	medium
Version:	3.5.7.2 release
Hardware:	All
OS:	All
Whiteboard:
Crash report or crash signature:		Regression By:

Description leprechaun 2016-01-19 19:48:38 UTC

I have LO 3.5.7.2 in an Ubuntu 12.04 32 bit platform, installed from standard repositories.
I pasted a percentage copied from a pdf (or from a rough text file) into a cell. Suppose A1. 
Something like this:
13%
There is no white spaces nor form feeds into the cell, I carefully checked.
I can make arithmetic with this cell (f.i. =A1*2 gives 0,26).
If I format the cell as a percentage, LO adds a ' at the start of the cell:
'13%
transforming thus this cell into a text. This cell become unusable, arithmetic doesn't recognise it as a number. Text to number extension say "nothing to do".
I have to erase the ' by hand as the sole solution.
If I don't format the cell as a %, I can't modify the precision of the number (number of digits after the , (Italian) or . (English). So, apparently, from an arithmetic point of view, LO treats this cell as a percentage, but is is unable to modify precision, considering it as text from a different point of view.
This doesn't happens if I input the same string (13%) from the keyboard.
You can imagine when a user like me have to paste a huge range of such data.
No way to transform the whole range into a range of % cells, but changing every cell one by one by hand. Search and replace doesn't find the ' at the beginning of the cell, and Text to number extension is unable to detect the '13% as a string, and writes "nothing to do".
This behaviour is crazy and incoherent.

Comment 1 Eike Rathke 2016-01-19 20:23:28 UTC

That means that before or while pasting 13% the cell was formatted as Text, effectively forcing the content to type text instead of number (and the content probably being left aligned). Formatting the cell to some number type does not change the content, but then the leading ' indicates that the content *could* be interpreted as a number. The ' is not part of the cell content, hence searching for it is not possible, but once the cells are formatted as number the content can be converted from text to number by selecting the cells and then use Find&Replace:

Search For: ^.+
Replace With: $0
Under Options activate
* Current selection only
* Regular expressions

and then hit Replace All.

Another possibility is to install this useful extension:
http://extensions.libreoffice.org/extension-center/ct2n-convert-text-to-number-and-dates

I don't remember how 3.5 (which btw is rather old) handled pasting text, but in a more recent 4.4.7 or 5.0.4 pasting 13% text yields a numeric value and not text.

Comment 2 leprechaun 2016-01-22 14:56:38 UTC

Thank you very much.
I could test this behaviour in a different system, running the last Mint LMDE, with Libreoffice version 3.7 ...
Pasting a 13% will bring to a numeric cell formatted as a percentage, no problems at all.
In my 3.5.7.2 nevertheless none of yours two suggestions works. Find-replace using regular expression doesn't remove the ' (it is apparently a "virtual" sign, not text), nor the extension you suggested. I tried these solutions by my own from the beginning, but no avail.
I have to upgrade my ubuntu 12.04 OS, because this means it is obsolete.

Regards