How to accept space in numbers

[ArthurRainbow]

The data I have writes a number as 12 345,67. This is valid and non ambiguous in French. I’d like to be able to copy paste it into libre office and that it gets understood as a number. It seems that the spaces cause it to be misunderstood.
Worse, If I select those numbers, and try to do a search and replace for the space character, replacing it with the empty string, nothing occurs, it seems the space character can’t be found in those cells with numbers, it is only found in cells with text.

What is the right solution if I want to keep using libreoffice without having to manually clean all data please?

[sokol92]

Your message contains two types of questions: entering a value into a cell and searching for cell values. Let’s first look at entering a cell value.
A non-empty cell in Calc that doesn’t contain a formula or an error value can contain two types of values:

• number
• string

The value type is determined when the value is entered into the cell and doesn’t change if the cell format is subsequently changed.
In interactive mode, when the user enters a cell value, Calc takes locale settings into account. For the fr_FR locale, the decimal separator is a comma, and the thousands separator is a non-breaking space. When entering a number, the user can also specify a regular space as the thousands separator.
Let’s test this. Create a new Calc document and enter your text 12 345,67 in cell A1. After entering it, the cell should display 12345,67, the right alignment indicates that the cell contains a number, not a string.
Did you get the same result?

[ajlittoz]

@ArthurRainbow

If I select those numbers, and try to do a search and replace for the space character, replacing it with the empty string, nothing occurs

Going along @sokol92’s path, this suggests the “space” in your number is a U+00A0 NO-BREAK SPACE, not a regular U+0020 SPACE.

You didn’t mention your OS name (nor the exact LO version). Window$ tends to frequently produce non-breaking spaces (instead of regular space) due to an unfortunate keyboard configuration regarding “modifier” keys (Shift, Control and Alt). Also, if your numbers were pasted from web sites, there is a high probability that the “inner” space is in fact a non-breaking space.

[schiavinatto]

Please post a sample file of your data. This will make it easier to verify and suggest a solution.