Can't convert text to numbers because of spaces

[mummo]

I have a table with about 1200 rows and the data is from a pdf file. The numbers in the original pdf had spaces as 1000 separator, so now I have text cells with "12 000" for example. How do I remove the spaces so I can convert them to numbers?

Find & replace says there is no instances to replace and =SUBSTITUTE(A1;" ";"") does nothing.

 

[Joe4]

If you look at the formatting of cell A1, what does it show the format to be?

And what does this return?

=LEN(A1)

Also, what does this formula return?

=CODE(LEFT(RIGHT(TRIM(A1);4);1))

 

[mummo]

If you look at the formatting of cell A1, what does it show the format to be?

And what does this return?

=LEN(A1)

Also, what does this formula return?

=CODE(LEFT(RIGHT(TRIM(A1);4);1))

I checked with 2 different examples, both with the value 28 000, other showed format as text and the other as number.
Length was 6 on both occasions.
Code returned 160 also on both.

 

[Sufiyan97]

Try

Edit: Change comma to ";"

I thouhgt @Joe4 is away that's why I stepped in, otherwise I would not have.

=--SUBSTITUTE(A1;CHAR(160);"")

 

[Joe4]

Code returned 160 also on both.

Code 160 means that it is not a normal space, it is a special non-breaking space (common on files downloaded from the internet or other sources).
See: ASCII table - Table of ASCII codes, characters and symbols

So try this instead to convert the entry to a number:

=SUBSTITUTE(A1;CHAR(160);"")+0

 

[mummo]

Try

Edit: Change comma to ";"

I thouhgt @Joe4 is away that's why I stepped in, otherwise I would not have.

=--SUBSTITUTE(A1;CHAR(160);"")

Code 160 means that it is not a normal space, it is a special non-breaking space (common on files downloaded from the internet or other sources).
See: ASCII table - Table of ASCII codes, characters and symbols

So try this instead to convert the entry to a number:

=SUBSTITUTE(A1;CHAR(160);"")+0

Thank you guys, this was the problem and now it works!

 

[Joe4]

You are welcome!
Glad we were able to help!