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