After an import of a CSV file, as there has been no way to define a column as “date/time” with a custom format during import, LibreOffice added a ’ (single quote) in front of all cells of a column. All cells are containing “German” date/time (TT.MM.JJ HH:MM), and I’ve formatted the cells or the complete column to date/time (TT.MM.JJ HH:MM). But because of the leading ’ no values are recognized as date/time by CALC, no calculations possible etc.
If I try to search and replace with regex mode and search for ^’ or °’ CALC tells me “not found”.
How can I get rid of the leading ’ ? With as less effort as possible, changing hundreds of rows manually does not make fun
PARTIALLY SOLVED: Using import format “Datum(TMJ)” works for the TT.MM.JJ HH:MM column. Fine, no search and replace needed anymore.
But I have a another column with cells containing a time length, format MM:SS.
- when setting this CSV column to “Datum(TMJ)”, I’m getting this length formatted as HH:MM:SS, and “shifted” to left. So e.g. an original 00:54, which means 0 minutes, 54 seconds, becomes 00:54:00, seconds are now minutes. Any hints?