Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

CALC: How can I replace a leading ' (single quote) in a range of cell, with a regex

After an import of a CSV file, as there has been no way to define a column as "date/time" with a custom format, 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)m 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 be CALC, no calculations possible etc.

If I try to search and replace with regex mode and search for ^' ior °\', CALC tells me "not found".

How can I get rid of the leading ', which as less effort as possible, changing hundreds of rows manually does not make fun :-(

CALC: How can I replace a leading ' (single quote) in a range of cell, with a regex

After an import of a CSV file, as there has been no way to define a column as "date/time" with a custom format, 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)m HH:MM), and I've formatted the cells or the complete column to date/time (TT.MM.JJ HH:MM), but HH:MM). But because of the leading ' no values are recognized as date/time be by CALC, no calculations possible etc.

If I try to search and replace with regex mode and search for ^' ior °\', or °\' CALC tells me "not found".

How can I get rid of the leading ', which ' ? With as less effort as possible, changing hundreds of rows manually does not make fun :-(

CALC: How can I replace a leading ' (single quote) in a range of cell, with a regex

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?