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

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?

edit retag reopen merge delete

### Closed for the following reason the question is answered, right answer was accepted by michaelof close date 2017-04-10 01:34:54.082148

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.

( 2017-04-09 19:47:20 +0200 )edit

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

( 2017-04-10 12:28:36 +0200 )edit

Sort by » oldest newest most voted

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

more

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!

( 2017-04-10 01:33:12 +0200 )edit

@michaelof:
(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.