How to remove a plain text protecting single quote from all the selected cells in LibreOffice Calc?

Question

I've imported a CSV file having the first column to be date-time values in ISO 8601 format like

2012-01-01T00:00:00.000Z

for the first moment of the year 2012.

Then, willing to make LibreOffice to recognize the format (as I was looking forward to plot a diagram), I've selected the column, chosen Format Cells... and entered the custom time format as

YYYY-MM-DDTHH:MM:SS.000Z

And this seems to work if... I edit a cell to remove a hidden single-quote from its beginning (which serves to protect a cell content from being interpreted) as all the newly formatted cells now store values like

'2012-01-01T00:00:00.000Z

(note the single quote - it is only visible when you edit a particular cell).

And I am to do so for all the cells in the column. How can I automate this?

UPDATE: I've already found a solution for the particular case of mine: it helps to set a column format to "time" in the CSV import dialogue. But I am still curious how could this be done in case I wouldn't have the original .csv data file to import but only the .ods file with the data already imported without the format specified at the import time.

Answer 1

You can remove the leading single quote (which actually isn't part of the string in the cell) using a regex-based search and replace:

• Search for all characters between the start and end of the string ^.*$
• replace with match &

Answer 2

By the use of a little computer formatting and search magic trick (programing technique if you will) called a Regular Expression or regex for short. For examples please see - http://www.regular-expressions.info/examples.html

Answer 3

Actually, you need a to quote the dollar sign first.

Basically libreOffice is prepending a single quote in the field, for some unknown reason. So, you need to replace anything up to the dollar sign with nothing. So, use the regex of ^.*\$ and replace it with nothing. Worked for me.