How to remove weird symbol infront of numbers?

[UseIT]

Hello,
i hope someone here has a solution to the following problem, because it drives me nuts.
I have lots of cells with numbers in them, they look normal but they are not recognized as numbers.
Instead they all got a weird symbol in front of their number, looks like ', but it is not that symbol.
At 1st the symbol is not even visible, i need to clear direct formatting and then enter the cell with cursor to edit it, only after that i can see this weird symbol '.
If i then copy it and use it at find and replace function, it is not found anywhere.
If i copy paste those cell and select numbers only, nothing is written into cell, the entire cell is recognized as text.
The only thing which sometimes (but not for all cells) works is Data > Text to Columns.

Either this is not actually a symbol, meaning ASCII character, but instead a signal, telling me there is more data attached to the cell.
Then my question is, how do i remove that data, i only need the numbers.

Or it is a character and for some weird reason find+replace cant find it, then my question is how do i filter the cell, so that only numbers remain. (keep in mind, paste>only numbers does not work)

Thank you

[mikekaganski]

FAQ

[UseIT]

Thanks,
so data is imported as textual content. In this case the Input is preceded with a leading ’ apostrophe which is not part of the cell content and can therefore not be found.
The way i understand it, the following “Find: .” (any single character) does not find the leading ’ apostrophe either but those steps rather trigger a reinterpretation of the cells. Or does the regular expression “.” find it?

1. Clear Direct Formatting
2. apply a number format
3. Find: .+
4. check Regular expressions
5. Replace: $
   Anyhow this works, thank you. I need to dig more into regular expressions xD

However i wish i don’t have to repeat this on all my sheets.
Is there a way to adjust the “import data as textual content” part, so that it does not add the leading ’ apostrophe which is not part of the cell content?

[EarnestAl]

@UseIT

Is there a way to adjust the “import data as textual content” part, so that it does not add the leading ’ apostrophe which is not part of the cell content?

I suppose it is currency that is causing the issue. Make sure the Detect special numbers box is ticked in the Text Import dialogue. Make sure also the locale is one that uses that currency. The below will import into English UK as numbers (without currency symbols). This is one reason why it is best to have the symbols only in the heading row, not in the data.

[UseIT]

Thx, but not the automation solution i hoped for.
In our case we export data via the matlab function writecell() directly into a specific sheet.
Anyhow we have settled with the 5 steps in the other post above to get rid of the weird ’ apostrophe, which again is not really a symbol or character but something else and it is not even visualized consistently but only shows for some cells after one edits it. Aahhh it gives me headache… bb

[mikekaganski]

Since you didn’t provide a sample data (generated by mathlab, not imported to Calc already!), there was no way to suggest you a method to improve your 5-step workflow.

[Wanderer]

@UseIT

we export data via the matlab function writecell() directly into a specific sheet.

If it is not possible to improve your input from “matlab function” You can take the string and clean it up for example with SUBSTITUTE() to remove unwanted characters like € or $.
.
The “cleaned” string can then be converted to a real number with VALUE(). Your local currency may be directly recognized by value. As you have not shown any samples from your data you have to try yourself…

https://wiki.documentfoundation.org/Documentation/Calc_Functions/SUBSTITUTE
.
https://help.libreoffice.org/latest/sq/text/scalc/01/func_value.html