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

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.

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

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

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.

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.

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.


See also this FAQ.