Pasted percentages contain symbol ' and can't use them as values until removing it

[oilsea]

It happened to me in different files that when I paste a column of percentage values, they appear with the symbol ’ as first digit, for example:

'0.59 %

If I do mathematical operations on such a value, there is no problem, but if I use the percentage as a range of values for, say, bar graphs, no bar shows unless I remove the ’ symbol from the percentage cell(s).

PS: I adjusted the graphs y-axis length so that the values would be visible (the y-axis is max 5%).

What is that symbol? Why is it pasted together with a percentage value? How can I have it removed/ignored (automatically) for graphs?

Thank you for your hints.

[PKG]

Was bedeuten die Hochkommas (Apostrophen) vor Zahlen, Gelbeträgen oder Kalenderdaten, und wie werde ich sie los?

Für Zellen in Calc gibt es dreierlei Inhaltstypen: Zahl, Text und Formel. Falls eine Formel vorliegt, gibt es wieder drei Ergebnistypen: Zahl, Text oder Fehlerwert. Wenn eine Zelle scheinbar eine Zahl enthält, bei Bearbeitung aber ein Hochkomma davor anzeigt, dann ist der Inhaltstyp ‘Text’. Geschriebene Zahlen sind eben auch Zeichenfolgen, und in diesem Sinne Text. Gibt man eine Zahl mit der Tastatur ein, liegt zunächst immer eine Zeichenfolge vor, und erst bei Beendigung der Eingabe kann Calc …

[Villeroy]

You imported text wrongly. In the import dialog for text or html you have to tick “Detect special numbers”.

[oilsea]

@Was bedeuten die Hochkommas (Apostrophen) vor Zahlen

bei Bearbeitung aber ein Hochkomma davor anzeigt, dann ist der Inhaltstyp ‘Text’.

Thank you for the hint, it was seen as a string indeed. The upper comma disappeared by applying =Value()

[oilsea]

@Villeroy

You imported text wrongly. In the import dialog for text or html you have to tick “Detect special numbers”.

It was a copy-paste from Population - Worldometer . not an import. Maybe that is why I can only do a paste special, and there I can’t find the option "Detect special numbers” the you mention.

But when in Paste special I selected “only numbers”, the destination cells would be empty. That was the final hint that the origin values were seen as strings.

[Villeroy]

I copy the first table from China Population (2023) - Worldometer
When I paste the selection, I’m prompted if I want to import unformatted text or html.
Both options lead to a second dialog with import options. On this dialog it is necessary to check “Detect special numbers” AND the right import langauge. With this particular web site some “English” language is the right choice because of the decimal points and the commas as thousands separators. If there were any dates like 12/31/1999, “English (USA)” would import correct dates, in case of 31/12/1999 any other English variant would do the job.

After importing the first table from China Population (2023) - Worldometer I call menu:View>Highlight Values [Ctrl+F8] which highlights all the columns below the header row in blue font indicating that all these values are numeric, thus properly imported.

[oilsea]

@Villeroy

I copy the first table from China Population (2023) - Worldometer
When I paste the selection, I’m prompted if I want to import unformatted text or html.
Both options lead to a second dialog with import options.

There might be my error! I did not notice the tickbox when I copied it the first time from the site, and all the following attempts to paste and have the options, I was mistakenly copying from my own spreadsheet (my first paste) instead of the original.

@Villeroy

menu:View>Highlight Values [Ctrl+F8] which highlights all the columns below the header row in blue font indicating that all these values are numeric, thus properly imported.

Beautiful!

[erAck]

See also this FAQ.