Can Formulas use a certain Digit, like the 1st or 2nd Digit, in all Cells in a Range?

[Arnold]

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

 

[Ron Rosenfeld]

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

 

[Arnold]

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

 

[Ron Rosenfeld]

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.

Being more specific in your requirements, as you are below, will bring a more
applicable answer, usually.

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)

=SUMPRODUCT(--LEFT(TEXT(AZ09:IV09,"000"),1))

M
=sum 'the 2nd digits in all cells' (AZ09:IV09)
=SUMPRODUCT(--MID(TEXT(AZ09:IV09,"000"),2,1))

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

=SUMPRODUCT(--MID(TEXT(AZ09:IV09,"000"),3,1))


as well as other general formulas requiring numeric values:

It is not clear to me what you want to do with the examples below. But perhaps
you can reason from the above solutions.

F
=count(AZ09:IV09)

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

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


Thanks once again

--ron

 

[Arnold]

THANKS RON!!

 

[Ron Rosenfeld]

THANKS RON!!

You're welcome. Glad to help.
--ron