Since LO 7.3 on Fedora I can’t seem to replace ’ at the beginning of the value in cells.
I import table from HTML, and have dates in one of the columns, and LO import (text to columns) adds an apostrophe before, so to be defined as text, but I have further formulas that calculate values based on this entries.
I used to be able to do CTRL-H (Search and replace) and used ’ in search field and & in replace field, and check-marked Regular Expression, and it replaced all selected cells, but no more in newer version.
Is there some switch/option in settings, or something changed between versions.
I’ve also tried to put in formula =right(A10,10) which got the value but still defined as text. How to convert it in the quickest manner to number
Instead of fixing wrongly imported data, you should import them correctly.
“Detect special numbers” will interprete dates, times, currencies etc.
The right language setting takes care of things like 9/22/2022 vs. 22/9/2022 or decimal 1.23 vs. 1,23 etc.
1 Like
The apostrophe is not part of the text, It is a marker for any numeric text that should not be evaluated as a number. Same as in Excel since 30+ years. Same as in Visicalc of 1979 with double-quote as marker. With that tag you can also enter text with a leading = which is impossible to do otherwise.
Text functions (such as RIGHT) return either text or position numbers of matching text.
VALUE() converts text to number in the context of the current office locale.
VALUE(“1,234”) returns different values when it interpretes the comma as decimal separator (German, French, Russian etc) or as thousands separator ( Chinese, English, Japanese etc.).
Function NUMBERVALUE can be used with a specific decimal separator. NUMBERVALUE
However, it is preferable (and much easier) to do the import right. Always keep the original files (csv, html) until you are sure that you did the right thing.
I do copy paste from html table, and I don’t have a problem with proper formatting (coma, or dot) it automatically puts the apostrophe on all the dates.
The formula =VALUE(RIGHT()) solves the issue, thanks, but I need to have an extra column there.
I did also try to do change to number/date… but it keeps apostrophe on.
So it means I can’t get rid of apostrophe…
Funny thing was when I did Column A (Imported with apostrophe) column B formula =VALUE(RIGHT(A2,10)) and it was shown as a value fine, but I did copy of Column B and paste special Values to Column C and it added an apostrophe again.
I can not confirm this issue. Paste-special numbers pastes the numeric results of =VALUE(text) formulas.
1 Like
You are right. I did copy only cells with formula =RIGHT(A10,10), and than it added apostrophe
First change the format of these cells from text to the default format - either reset the formatting with Ctrl+M or go to format cells Ctrl+1 and change the format to Number-General. If after this the numbers have not yet become numbers (and this can happen), then use Ctrl+H
2 Likes
Thanks, that solved it! 
But what does it mean .+ ? I’m sure it’s not random.
1 Like
Treat it like “the contents of the entire cell”
1 Like
OK I see (it makes sense . any character and + any number of times), but than what is the meaning of the & symbol ?
