Show decimals until different than zero

Example:
Your [A1] is 0.0008456, please enter below formula in [B1]

Formula:

Please Login or Register  to view this content.

Select the range
Format cells --> Custom --> Enter 0.##########
Number of #'s depending the maximum digits to be displayed. In this example I have given 10 #'s. It will display maximum of 10 digits.

As a scientist, naturally my favorite "2 significant figure" number format is scientific format "0.0E+0". But I also recognize that most people don't like scientific format so that probably doesn't help. I like that number formatting does not remove information from the underlying value so that it can still be used in downstream calculations.

The simplest formula approach for "round a number to 2 significant figures" that I've found is to convert the number to text in scientific format, then back to a number =VALUE(TEXT(number,"0.0E+0")) [format as general]. Recognize that this approach completely removes all of the other information, so I only use this as the final step in a sequence of calculations. If this number needs to be used in downstream calculations, keep a copy somewhere else to its full precision for those calculations.

In many ways, I have not found a simple way to display numbers to 2 sig figs, but those or the other solutions come close.