Find first digit of value

[Tom]

I have a large vector of values with a wide dynamic range (10^-6 to 10^-
16). For a particular application, I need to retrieve only the first
digits of the values, yielding an output vector that contains only the
values 1 through 9. I can do this manually fairly easily by first
multiplying by an appropriate value to bring the result to a whole number,
then truncating the fractional part.

I'm sure that there must be a way to automate this in Excel, but I can't
figure out how to do it. The problem seems to lie in my inability to
separate the characteristic and mantissa.

Any ideas?


--Tom

 

[Ron Rosenfeld]

I have a large vector of values with a wide dynamic range (10^-6 to 10^-
16). For a particular application, I need to retrieve only the first
digits of the values, yielding an output vector that contains only the
values 1 through 9. I can do this manually fairly easily by first
multiplying by an appropriate value to bring the result to a whole number,
then truncating the fractional part.

I'm sure that there must be a way to automate this in Excel, but I can't
figure out how to do it. The problem seems to lie in my inability to
separate the characteristic and mantissa.

Any ideas?


--Tom

=LEFT(TEXT(A1,"0.000000000000000E+0"),1)

Note that if in the TEXT function, you have fewer digits in the decimal
portion, some values may be rounded up in the first digit.
--ron

 

[Tom]

=LEFT(TEXT(A1,"0.000000000000000E+0"),1)

Note that if in the TEXT function, you have fewer digits in the
decimal portion, some values may be rounded up in the first digit.
--ron

Thanks. I assume that, since I have 2-digit exponents, I ned to end it
with "...E+00", rather than a single zero.


Regards,

Tom

 

[Ron Rosenfeld]

Thanks. I assume that, since I have 2-digit exponents, I ned to end it
with "...E+00", rather than a single zero.


Regards,

Tom

Since you are just looking for the first digit, I don't think that makes any
difference.

--ron