0

As it can be seen below, I have cells with dates and numbers. The latter are in format "Number". However, the numbers have an apostrophe (') at the beginning of each cell. This can be seen in the red circle.

enter image description here

This symbol makes the cell not to be an actual number. When eliminated, numbers are recognized, as in the first, 9.4 example, with the alignment to the right. Then I thought, well, I will eliminate them using the "Find & Replace" option. However, LibreOffice can't find the symbol! I have hundreds of cells, so a manual solution is not plausible.

UPDATE: I have done several tests to pin down the problem:

  • I have two keyboard layouts. Tried with both. No change.

  • Save file in different format, nothing.

  • In the row CR I have a =RIGHT(CQ#,3) formula to get rid of the apostrophe but it does not work, as it can be seen in the image.

  • If I create a new cell manually with, for example, '20, it still cannot find it.

  • Interestingly, concatenate formula give weird results (values taken from cells, not input as below):

    =concatenate(20,'10)=2010

    =concatenate("asd",'10)=asd10

  • Even more interestingly, if the symbol ' is together with a random collection of symbols, it can be found: for example, in a cell containing '*^£@'#!('

  • This could be the crucial evidence. If I reformat the cell as a "Text", then the apostrophe disappears (but the value continues at the left of the cell, so not a number!). However, if I reformat it back to number, the apostrophe comes back. This is very weird stuff......

4
  • I tested it right now. The "Find & Replace" works fine. Please make sure you enter exactly the same character and check the keyboard language if you have multiple keyboard languages.
    – frogatto
    Aug 11, 2016 at 11:12
  • Thanks, but there is no trick with the character. I can even copy and paste it, and it does not find it. See updated question.
    – user308164
    Aug 11, 2016 at 11:21
  • IMHO, the best solution to remove the single quote is using the Text to columns" feature from menu data - see this answer: superuser.com/a/684084/84724
    – tohuwawohu
    Aug 11, 2016 at 17:27
  • That is the solution I give. Check answer.
    – user308164
    Aug 11, 2016 at 17:31

3 Answers 3

1

I assume you pasted the numbers from somewhere? Usually this kind of problem is due to a copy/paste manipulation. I remember having issues pasting numbers from SPSS or other softwares into excel/libreoffice.

Have you tried special paste > values only ?

1
  • I took them from generated .xls files from a query. They already come with the apostrophe. No paste special subcommand eliminates it. In fact, pasting numbers only pastes nothing!
    – user308164
    Aug 11, 2016 at 11:36
1

I could finally reproduce the problem. The ' character is problematic. My solution for finding and replacing them:

  • Click on a cell containing the character and select and copy the character in the text box above.

  • Hit Ctrl+H to show up the "Find & Replace" dialog.

  • Paste the character into the "Search For" text box.

  • Click "Other Options" and check "Regular Expressions".

  • Click on "Find Next". Now you should be able to find them.

5
  • I'm afraid it does not work for me...
    – user308164
    Aug 11, 2016 at 11:56
  • @luchonacho Did you test?
    – frogatto
    Aug 11, 2016 at 11:57
  • Yes! I tried everything on the Other Options box. :(
    – user308164
    Aug 11, 2016 at 12:10
  • @luchonacho Seems it only works when you copy and paste the character, not typing it by keyboard.
    – frogatto
    Aug 11, 2016 at 12:20
  • Found it... text to columns solved it. Other solutions here.
    – user308164
    Aug 11, 2016 at 12:22
1

Thanks to the others who responded, but their answers did not help. It happens that the apostrophe is a flag on the cell (kind of a meta information) and as such it is not part of the standard cell content. That is why it cannot be found using standard methods. This also explain all the behavior I mention at the bottom on my question.

There are several ways to solve this. A quick trick is to select the cells in question and select Data -> Text to columns. There, use the symbol in question (') to separate columns. The result is the elimination of the flag and the transformation of the data into numbers.

Alternatively, an "advanced", or regex-based search can find the element. This search looks beyond the cell content. In this particular case, the solution is to look for .+ and replace with &, as can be seen below. enter image description here

Note: I got this answer from here. However, the accepted answer does not work for me. In any case, I hope this help others, as this issue is not documented in AskUbuntu.

You must log in to answer this question.