I am trying to create a significant figure calculator, but I need some help.
I would like to do it with Built-in functions if possible.

I have formatted a column as text. The next is =FIND(".",A1), which will
return a number if a decimal was found. The next is =COUNT(B1).

Col C will be a Boolean (1 if there is a decimal, 0 if there is not).

If there is a decimal, I want to find the first non-zero digit from the LEFT
and count all digits from that one to the end (reading left to right). For
example, 0.0070 should ignore the 0.00 and count only the 70 on the right
(and show 2 for the number of sig figs).

If there is no decimal, I want to find the first non-zero digit from the
RIGHT and count all digits from that one to the end (reading right to left).
For example, 705000 should start on the right, skip the leading 0's and then
count the 5, the 0, and the 7 going right to left to return a value of 3.

I think I might have to create a VBA function, but I have limited experience
with that. If I did, I would set a flag (foundNonZero = 0), create a for
loop (for i = 1 to string.length) loop through until the first non-zero is
found, and then return string.length - i +1

If it did not have a decimal, I would loop from string.length to 1 instead.

Any suggestions?

Thanks!!

- Jeff

Does this formula do what you want?

=LEN(ABS(SUBSTITUTE(A1,".","")))

--
Rick (MVP - Excel)


"Jeff Ciaccio" wrote in message
...
I am trying to create a significant figure calculator, but I need some
help. I would like to do it with Built-in functions if possible.

I have formatted a column as text. The next is =FIND(".",A1), which will
return a number if a decimal was found. The next is =COUNT(B1).

Col C will be a Boolean (1 if there is a decimal, 0 if there is not).

If there is a decimal, I want to find the first non-zero digit from the
LEFT and count all digits from that one to the end (reading left to
right). For example, 0.0070 should ignore the 0.00 and count only the 70
on the right (and show 2 for the number of sig figs).

If there is no decimal, I want to find the first non-zero digit from the
RIGHT and count all digits from that one to the end (reading right to
left). For example, 705000 should start on the right, skip the leading 0's
and then count the 5, the 0, and the 7 going right to left to return a
value of 3.

I think I might have to create a VBA function, but I have limited
experience with that. If I did, I would set a flag (foundNonZero = 0),
create a for loop (for i = 1 to string.length) loop through until the
first non-zero is found, and then return string.length - i +1

If it did not have a decimal, I would loop from string.length to 1
instead.

Any suggestions?

Thanks!!

- Jeff