formulas equating to numbers, then pasted as values, seem not to be quite numbers...

[lotusman]

I am puzzled by something I've not noticed before, and that fact of only seeing it now also surprises me.
If I have a formula, which strings a few digits together, e.g. left(a4,1)&mid(B4,2,1)&right(C4,1) which displays as 123, and I copy it as a value to the same (or even to another) cell, say, to D4, then the strange anomaly happens that if I type into E4 that D4=123, I will get the result FALSE rather than TRUE. Something carrying over in some sense as 'non-numerical' seems to be at play from the formula pasted in (to D4) as a value. If I type =value(D4)=123, I will get TRUE. Why does the 'number' not quite 'answer' as a number?

[jason.b75]

Strigning numbers together with & creates a number formatted as text, which is not the same as a true number.

You should also notice that it is aligned to the left of the cell, while a true number is aligned to the right (unless you have changed the formatting).

Any of the following methods will correct it at the source.

=--(left(a4,1)&mid(B4,2,1)&right(C4,1))

=value(left(a4,1)&mid(B4,2,1)&right(C4,1))

=(left(a4,1)&mid(B4,2,1)&right(C4,1))+0

=(left(a4,1)&mid(B4,2,1)&right(C4,1))*1

[lotusman]

I get it, set the values at the 'formula' stage, within the cell. Copying a string as a value will not quite work. Pasting as a value is really about formulas with numbers, come to think of it.
Thanks, Jason.