Remove apostrophe and space before date in column

[stragu]

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: https://cloud.indie.host/index.php/s/4sl2Y9IjunaOLKi)

Using version 5.1.6.2

[karolus]

Select the column and do →search and replace:

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

[stragu]

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

[karolus]

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.

[valerieanderson]

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.

[wanconm]

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

[cpc1]

I agree with this

[DaveS1]

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…

[meepmeep]

You can do a find replace with

^. in find
and
&
in replace

and it will remove the tick

[meepmeep]

source: remove apostrophes before numbers in OpenOffice Calc – Ryan and Debi & Toren

[r0berts]

This works wonderfully. Saves a lot of time. Also do not forget to tick “Use Regular Expressions” in search options. This is regex replacement (it says something like “take what you find and put it back in place”), so if you do not tick that little box, there is no way this can work.

See List of Regexes LO

[realfoodman]

This worked for me as well. I also did have to check the “Regular Expressions” checkbox in the Find and Replace dialog.

[Albert25]

• Select the column
• In the menu, select “Data” > “Text to Columns…”
• In the “Text to Columns” window, under “Fields” at the bottom:

• select the column
• set the “Column type” to “Date” with the appropriate format (Date (DMY), Date (MDY) or Date (YMD))