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?

Rick

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

Something like this:

=LEFT(TEXT(A1,"0.0"& REPT("0",50)&"E+0"),1)

You can replace the 50 with some number that is greater than the maximum
number
of decimal places you might have in your original number.

That will work as long as you put A1 in an ABS function first... the OP said
he had negative values too.

Rick