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


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


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


(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.

5 Answers 5


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 &
  • 4
    Thank heavens for the internet and for you, sir. This was exactly right.
    – queso
    Mar 5, 2012 at 21:44
  • 5
    How the heck does it work if it even has no actual quote symbol (nor its code) in it?
    – Ivan
    Mar 26, 2012 at 0:03
  • 5
    @ivan - It's a messy hack, but it doesn't actually replace the quote at all. Basically, from the regex engine's perspective, the quote isn't there at all (it's a special flag on the cell). As such, what it does is match the entire contents of the cell (which doesn't have the quote in the string, remember). Then, what it does is delete the cell contents (which includes clearing the "plain text" flag), and then reinsert the contents. The data autodetection engine then sees a number, and interprets the reinserted data as such.
    – Fake Name
    Jun 5, 2013 at 22:10
  • It's worth noting that this will probably delete any formatting that relies on characters that set special cell flags, as well as the "plain-text" number flags.
    – Fake Name
    Jun 5, 2013 at 22:10
  • 2
    Thank you. This is so weird, it beggars belief. Why on earth there should be this single quote character after importing?
    – r0berts
    May 16, 2015 at 13:47

From the "Data" menu, choose "Text to columns".

  • 5
    A cleaner alternative to the REGEXP method.
    – jgomo3
    Jun 22, 2014 at 19:03
  • Crucially, the "Text to columns" function allows altering the "type" of the cells. Sometimes it's stuck on "text" and no amount of re-formatting seems to fix it. Here's how: at the bottom of the dialog box under "Fields", click the column header which says "Standard", then select the desired type in the "Column type" drop-down.
    – sxc731
    Dec 1, 2016 at 12:53
  • The Regex didn't work in LibraOffice, but change the column type to "Text" from "Standard" using Text to Columns worked.
    – Exit
    May 24, 2018 at 7:35
  • Scratch that, as soon as I try to style the column as a number with 8 decimals, it adds the single quotes back in front. I give up, returning to Excel.
    – Exit
    May 24, 2018 at 7:41

An attempt for regex replacing of ^.\*$ with & crashed LibreOffice 5. Replacing of .\* with & worked fine.


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

  • 3
    you would be a cool dude if you could write an example regex.
    – Mikhail
    Nov 3, 2012 at 11:47

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.