Hi All,
Is there a way for formulas to use only the first numeric digit in all
cells, for example, to the right of col X? And likewise, is there a
way for formulas to work with only the 2nd or the 3rd numeric digits
in cells? Specifically, each of the cols past X contain a 3 digit
code. The digits may only = 0, 1, 2, 3, or 4. The cells may also be
blank or null. Each of digits in the cols. would need to be included
into the formulas. And, the range of cells (cols) will grow daily.
Any insight/example would be greatly appreciated.
Thanks very much,
Arnold

On Wed, 01 Aug 2007 17:12:46 -0700, Arnold wrote:

Hi All,
Is there a way for formulas to use only the first numeric digit in all
cells, for example, to the right of col X? And likewise, is there a
way for formulas to work with only the 2nd or the 3rd numeric digits
in cells? Specifically, each of the cols past X contain a 3 digit
code. The digits may only = 0, 1, 2, 3, or 4. The cells may also be
blank or null. Each of digits in the cols. would need to be included
into the formulas. And, the range of cells (cols) will grow daily.
Any insight/example would be greatly appreciated.
Thanks very much,
Arnold

What you describe can be done. You could use either TEXT functions, or perhaps
INT and MOD functions.

For example, the middle digit of a three digit number can be obtained with the
formula: =MID(TEXT(Y1,"000"),2,1).

12 -- 1 (The three digit number, in this instance, would be 012).


--ron

Thanks for responding Ron,

I searched the groups for what you recommended, reviewing a couple of
similar threads, including one that you contributed to: Use of Like
to extract data, and Detecting a number versus text. However, I
cannot see how to have formulas directly use these separate left-mid-
right values formatted as TEXT.

For instance, if the following values are in these cols:

AZ BA BB BC ...
201 323 434 <null

how could I write these formulas in cols such as:

L
=sum 'the 1st digits in all cells' (AZ09:IV09)

M
=sum 'the 2nd digits in all cells' (AZ09:IV09)

N
=sum 'the 3rd digits in all cells' (AZ09:IV09)


as well as other general formulas requiring numeric values:

F
=count(AZ09:IV09)

G
=countif(AZ09:IV09,"0")

H
=countif(AZ09:IV09,"0")


Thanks once again