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

A

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
 
R

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
 
A

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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top