In Excel 2016 I need to show zeros out to the leading non-zero digits and then just the two digits after that. For example:
0.00002423
should read0.000024
,0.242
should read0.24
,0.0032255
should read0.0032
,
and so forth.
Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this communityIn Excel 2016 I need to show zeros out to the leading non-zero digits and then just the two digits after that. For example:
0.00002423
should read 0.000024
,0.242
should read 0.24
,0.0032255
should read 0.0032
,and so forth.
Try,
In B2
formula copied down :
=0+LEFT(A2,MIN(FIND({1,2,3,4,5,6,7,8,9},A2&1/17))+1)
Edit :
1] 1/17 =0.0588235294117647 which is called Pandigital number that contains digits from 0 to 9
2] A2&1/17 is a short form of A2&1234567890 in which to shorten the formula length.
3] Other Pandigital number can also be used :
A2&5^19
A2&7^18
A2&3^45
..........
A2&1234567890
for clarity. Anyone seeing this code years later is going to be very confused otherwise.
– Darrel Hoffman
May 12 at 20:37