values in number format but actualy not

[mohammad10]

hi

i imported some numbers from external source into excel cells

when click on this cells excel shows number format  

but when using sum function returns 0 or #value and ...

i used text to column and value function to convert them to number format but didn't work

why such things happens in excel at all ?

why method's dose not work?

i attached the file please look at it

[#2]

@mohammad10 

try

[#3]

@mohammad10 -

it's likely due to the comma used as a thousands separator. Excel may be interpreting it as a text string. Comma is a DISPALY( Formatting) character not a character itself. =LEN(123,446) =6 not 7 but in your example it is 8 because it has a trailing space in addition to a manually entered comma( 6 character+1 comma+1 trailing space). See the attached image.

Hope this helps!. 

[#4]

thank you

please explain what was the problem and how you fix that

[#5]

thank you
you are right
how can i remove trailing space because trim function dose not work

[#6 · Accepted solution]

@mohammad10  wrote:  ``how can i remove trailing space``

It is not a trailing space per se.  But we can use LEFT(R7,7) to ignore the last character.

To convert the data, use =VALUE(LEFT(R7,7)).  More generally, =VALUE(LEFT(R7,LEN(R7)-1)).

The last character seems to be the Unicode character 8236 (U+8236).  You can see this in Excel with =UNICODE(RIGHT(R7,1)).

If your version of Excel does not support UNICODE (mine does not), in VBA, we write AscW(Right([R7],1)).