Find first digit of value
 

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

Find first digit of value
 

On Tue, 13 Nov 2007 17:40:04 +0000 (UTC), Tom wrote:

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

Find first digit of value
 

Ron Rosenfeld wrote in
:

On Tue, 13 Nov 2007 17:40:04 +0000 (UTC), Tom
wrote:

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

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

Find first digit of value
 

On Thu, 15 Nov 2007 20:49:56 +0000 (UTC), Tom wrote:

Ron Rosenfeld wrote in
:

On Tue, 13 Nov 2007 17:40:04 +0000 (UTC), Tom
wrote:

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

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