Cell number formatting doesn't work

[moty.98]

hi guys,

See in the attached file, when i try to sum up using sum function in B4, the values in B2 and B3, it doesn't work for me, even though I changed to format to number.
I think it's related to the - (negative) characters.
I'm trying to understand the reason for this one. How can i change B2 and B3 cells to be considered as numbers (without manually changing - sign with other - sign).


Thanks,

Moty

[MrShorty]

I put ISTEXT(B2) in column C, and both values came up TRUE() indicating that the actual cell values are text (note that simply changing number formatting by itself cannot change a number stored as text to a number). The SUM() function ignores text (including numbers stored as text), so the sum of two text strings is 0.

I put LEN(B2) into column D, which returns 4 (for what looks like a 3 character string). There is obviously a non-printing character in these values somewhere.

I put =CODE(MID(B2,1,1)) into column E and got 63 which is a ? character or the default value that the non-unicode ascii functions seem to return for any unicode character that is not also on the ascii list.

Since my older version of Excel does not have UNICODE() functions, that was as far as I got. The solution to your problem is to figure out how to get rid of that first character and then convert the text to a number. Something like =VALUE(RIGHT(B2,3)) would probably work, if your numbers are always 3 characters long and the first character in the string is always the non-printing extraneous character. If the possibilities are more varied, then you will need a more complex function to remove only the numeric part of the string. One way or another, getting rid of those extraneous characters is what you need to do.

[AlKey]

Try this
There is a char(63) attached before "-"

Please Login or Register  to view this content.

v	A	B
1	date	profit
2	9.10.2016	?-10
3	8.10.2016	?-20
4		-30.00

[moty.98]

Thanks guys. I really appriciate your answers. Very helpful!