Ask Your Question

Remove apostrophe and space before date in column

asked 2016-11-15 03:52:46 +0200

chtfn gravatar image

updated 2016-11-15 04:21:20 +0200

For some reason, an export of dates from a source (I think it was CSV export of a calendar from Evolution) gives me an apostrophe followed by a space before the actual dates.

I cannot figure out how to remove those two first characters from all the cells in that column. For some reason, I have absolutely no success using "search and replace".

I feel like it has to do with how the apostrophe is interpreted by Calc: it shows in the input line, but not when formatted in the cell (whereas the space actually does show). Looking for the character in "search and replace", be it in "value" or "formula", returns no result. Bug or feature?

See attached sample file to test. (Edit: it seems the attachment thingy does not work, here is the file:

Using version

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2016-11-15 10:18:13 +0200

karolus gravatar image

updated 2016-11-15 10:23:13 +0200

Select the column and do →search and replace:

search for ^_ (type an space instead underscore) replace with nothing, tick [x]regular Expression

edit flag offensive delete link more


Wonderful! That worked a treat. Do you know why the apostrophe could not be searched without using a regular expression, by any chance?

chtfn gravatar imagechtfn ( 2016-11-16 02:49:06 +0200 )edit

Its a singlequote, not apostrophe…
Its designed for implizit Textformat of any Input which can be interpreted otherwise as Number or Date.

type into cell 'any_number … you will see the quote only in the Formulabar not directly in the Cell.

karolus gravatar imagekarolus ( 2016-11-16 10:11:52 +0200 )edit

I would like to echo @wanconmgmailcom 's answer here and note that this no longer works. My workaround is to cut and paste the offending column into a text editor, change the column type to All->General, and then paste the column back in. Calc usually automatically recognizes the data as a date and I'm good to go.

valerieanderson gravatar imagevalerieanderson ( 2017-04-14 17:19:02 +0200 )edit

answered 2017-01-01 08:19:59 +0200 gravatar image

the above does not work there used to be a way copying a blank cell and using edit/ paste special and checking value and add and not even that works, the "value" does not appear under paste special and there is not need for these things to be so convuluted the "implizit Textformat" is a incredibly bad idea by someone who no doubt thinks they are smart (and may be) but someone who should not be allowed to make any user interface functionality type decisions and as a patented user-interface/human factors designer i'm in position to say so AND all "search and replace"s should have a provision for all special characters and should be explained clearly in help even if you have to enter it in hex or something from a table how bad is this, this is Microsoft bad, this is Trump ignorant, well maybe not that ignorant I am always promoting LibreOffice but this is a big time loss and big disappointment Someday I will find or someone will no doubt have a solution for this or I will just continue to loss time working around it but this is NOT good design

edit flag offensive delete link more


I agree with this

cpc1 gravatar imagecpc1 ( 2017-04-25 01:30:36 +0200 )edit

the "paste out to a text editor" method works for me, 3-20-2018. I wish I'd not had to do it, but it's not all that horrible. On pasting to libre writer - with no special 'paste-as' or other gymnastics required - it stripped the single-quote. Re-copy that column back into Calc, and bob's your uncle...

Dave S gravatar imageDave S ( 2018-03-21 04:33:27 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-11-15 03:52:46 +0200

Seen: 2,265 times

Last updated: Jan 01 '17