On Dec 9, 11:21 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
For all values larger than 1, =LEFT() does the job nicely but fails
when the values are larger than 0 but less than about 1.00E-19 [1]. I
also have some negative values.

You should be able to take care of the negative values by using the ABS
function before using the LEFT function. For example,

=LEFT(ABS(A1))

However, I do not understand what problem you are having with values
"larger
than 0 but less than about 1.00E-19"... can you clarify that statement
and,
perhaps, give an example or two of what you mean?

Yes, what I mean is this. =LEFT(1E-18) returns 0 whereas =LEFT(1E-19)
returns 1.

You could always do this...

=LEFT(SUBSTITUTE(SUBSTITUTE(ABS(A6),"0",""),".","" ))

Rick

All three solutions work perfectly. Thanks very much for everybodies
help.

--
Nige Danton