How to get first significant figure from a number in Excel?

Question

I have a column of numbers in Excel 2016. The numbers span many orders of magnitude, but are all positive. Some are less than zero. How can I return the first significant figure of each cell in a new column?

For example, for the number 1.9 the result should be 1. For the number 0.9 the result should be 9.

Things I've tried:

• Using LEFT() to get the first character. This works for values greater than 1, but for numbers between 0 - 1 it returns 0 (that is, LEFT(0.3, 1) returns 0). I've tried using this with scientific notation formatting and it returns the same result.
• I've searched Google and SO for solutions to this problem. There are many posts about rounding to significant figures, but I'm looking to truncate, not round.
• Reading through Office's online docs regarding scientific notation.

Answer 1

You could use scientific notation:

=LEFT(TEXT(A1,"0.000000000000000E+00"))

Note: You can only have 15 digits of precision in Excel so this should be OK.