Forum Discussion
Solved
values in number format but actualy not
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
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)).
5 Replies
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!.
- thank you
you are right
how can i remove trailing space because trim function dose not workmohammad10 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)).
thank you
please explain what was the problem and how you fix that
