Ask Your Question
0

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

asked 2017-04-09 17:40:17 +0100

michaelof gravatar image

updated 2017-04-09 18:00:17 +0100

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 flag offensive 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

Comments

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 gravatar imageLupp ( 2017-04-09 19:47:20 +0100 )edit

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

Lupp gravatar imageLupp ( 2017-04-10 12:28:36 +0100 )edit

1 Answer

Sort by » oldest newest most voted
1

answered 2017-04-09 19:53:00 +0100

Lupp gravatar image

updated 2017-04-10 12:40:27 +0100

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

edit flag offensive delete link more

Comments

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

michaelof gravatar imagemichaelof ( 2017-04-10 01:33:12 +0100 )edit

@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?

Lupp gravatar imageLupp ( 2017-04-10 12:32:58 +0100 )edit

Question Tools

1 follower

Stats

Asked: 2017-04-09 17:40:17 +0100

Seen: 531 times

Last updated: Apr 10 '17