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

[michaelof]

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?

[Lupp]

Most likely the apostrophe is not part of the text contained in the cell, but an indicator shown in the formula bar or when the cell is entered for editing to tell that something recognizeable as some kind of numeric entry was treated as text instead. Therefore the RegEx ‘F&R’ does not apply.
There is this thread in the German section. To avoid any misunderstanings you should attach a relevant example as an .ods. Your karma will surely be sufficient.

[Lupp]

The main part of this comment was also inserted as the leading part into my answer regarding the comment by the OQ.

[Lupp]

(Editing with respect to the comments by the OQ:)
Most likely the apostrophe is not part of the text contained in the cell, but an indicator shown in the formula bar or when the cell is entered for editing to tell that something recognizeable as some kind of numeric entry was treated as text instead. Therefore the RegEx ‘F&R’ does not apply.
(End Editing)

@michaelof: “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, …”

Very strange. If you got the standard import dialog for csv: Did you click on the column labels of the respective columns? Doing so you should have a choice in the ‘Column type’ field.

A very similar dialog will open if you use the ‘Text to columns…’ tool for one column at a time.

[michaelof]

Hi Lupp! Thank you for your answer and your comment!

To your answer: yes, of course I’ve clicked on the column label, to set to “Datum(TMJ)” As I wrote, I’ve detected first after asking here that “Datum(TMJ)” contains time info also, fine for me.

To your comment: Thank you very, very much for the clarification of the apostrophe as “hidden text”, not a part of RegEx ‘F&R’, clear now!

(I’ll mark your answer as “correct answer”, although your comment is what I was searching for.)

[Lupp]

@michaelof:
(1) I pasted the comment you found helpful into my answer.
(2) I would not confirm your ...clarification of the apostrophe as "hidden text"...
In the mentioned case the apostrophe is not at all part of the content of the cell, neither hidden nor otherwise. The fact that the cell’s content is text is stored in the Cell.Type property.

BTW Did you read the German thread?