Hi,

I'm just trying to count the cells in a range that have alphanumeric data.

example: asd, we, rty, 22r, 45aa, 1s, wer, tyu.

I need to count all cells in that range that have numbers.

If all of the ones with number have a number as the 1st character, then a
formula like this should work, just change the A1:A8 range to whatever range
you need:
=COUNTIF(A1:A8,"<A")


"Aris" wrote:

> Hi,
>
> I'm just trying to count the cells in a range that have alphanumeric data.
>
> example: asd, we, rty, 22r, 45aa, 1s, wer, tyu.
>
> I need to count all cells in that range that have numbers.

That works great if the number is at the beginning of the string, is there a
way to do it if the number is anyplace in the string ?

"JLatham" wrote:

> If all of the ones with number have a number as the 1st character, then a
> formula like this should work, just change the A1:A8 range to whatever range
> you need:
> =COUNTIF(A1:A8,"<A")
>
>
> "Aris" wrote:
>
> > Hi,
> >
> > I'm just trying to count the cells in a range that have alphanumeric data.
> >
> > example: asd, we, rty, 22r, 45aa, 1s, wer, tyu.
> >
> > I need to count all cells in that range that have numbers.

Someone sharper than me may come up with a one-formula solution, but about
the best I can do is recommend using a helper column.

In the helper column (assume you have 1st entry, like asd, in A1) on row 1,
put this formula:
=COUNT(1*MID(A1,ROW($1:$9),1))
and 'commit' (end) the formula by pressing the [Shift]+[Ctrl]+[Enter] key to
make it an array formula. Fill it down as far as required. make the $9 =
the longest possible string in your list that it may find. This formula will
return the number of numeric characters found in each string. Now you can
use this formula to get a count of entries in A that numbers in them (this
assumes that your helper column with the array formula from above is in
column X)
=COUNTIF(X:X,">0")

Hope that helps.

"pmartglass" wrote:

> That works great if the number is at the beginning of the string, is there a
> way to do it if the number is anyplace in the string ?
>
> "JLatham" wrote:
>
> > If all of the ones with number have a number as the 1st character, then a
> > formula like this should work, just change the A1:A8 range to whatever range
> > you need:
> > =COUNTIF(A1:A8,"<A")
> >
> >
> > "Aris" wrote:
> >
> > > Hi,
> > >
> > > I'm just trying to count the cells in a range that have alphanumeric data.
> > >
> > > example: asd, we, rty, 22r, 45aa, 1s, wer, tyu.
> > >
> > > I need to count all cells in that range that have numbers.

Thanks - every way I could think of required a helper column as well
thanks for the input

"JLatham" wrote:

> Someone sharper than me may come up with a one-formula solution, but about
> the best I can do is recommend using a helper column.
>
> In the helper column (assume you have 1st entry, like asd, in A1) on row 1,
> put this formula:
> =COUNT(1*MID(A1,ROW($1:$9),1))
> and 'commit' (end) the formula by pressing the [Shift]+[Ctrl]+[Enter] key to
> make it an array formula. Fill it down as far as required. make the $9 =
> the longest possible string in your list that it may find. This formula will
> return the number of numeric characters found in each string. Now you can
> use this formula to get a count of entries in A that numbers in them (this
> assumes that your helper column with the array formula from above is in
> column X)
> =COUNTIF(X:X,">0")
>
> Hope that helps.
>
> "pmartglass" wrote:
>
> > That works great if the number is at the beginning of the string, is there a
> > way to do it if the number is anyplace in the string ?
> >
> > "JLatham" wrote:
> >
> > > If all of the ones with number have a number as the 1st character, then a
> > > formula like this should work, just change the A1:A8 range to whatever range
> > > you need:
> > > =COUNTIF(A1:A8,"<A")
> > >
> > >
> > > "Aris" wrote:
> > >
> > > > Hi,
> > > >
> > > > I'm just trying to count the cells in a range that have alphanumeric data.
> > > >
> > > > example: asd, we, rty, 22r, 45aa, 1s, wer, tyu.
> > > >
> > > > I need to count all cells in that range that have numbers.

>is there a way to do it if the number is anyplace in the string ?

I searched through my extensive formula library and I was surprised to learn
that I had nothing for this.

So, I whipped up this formula but it seems overly complex for the task at
hand.

Create these defined names:

Nums
Refers to:
={0,1,2,3,4,5,6,7,8,9}

Array
Refers to:
={1;1;1;1;1;1;1;1;1;1}

Then:

=SUMPRODUCT(--(MMULT(COUNTIF(OFFSET(A1:A10,ROW(A1:A10)-1,,1),"*"&Nums&"*"),Array)>0))

Possible drawback: Depending on what version of Excel you're using the MMULT
function is limited in range size. Versions prior to Excel 2007 are limited
to no more than 5461 rows of data. No limit in Excel 2007.

I'm reluctant to use helper columns/cells unless there is no other way but
in this case I might even opt for the helpers.Well, maybe not!

--
Biff
Microsoft Excel MVP


"pmartglass" <(E-Mail Removed)> wrote in message
news:AFD66A17-0DF4-4DDA-A9E0-(E-Mail Removed)...
> Thanks - every way I could think of required a helper column as well
> thanks for the input
>
> "JLatham" wrote:
>
>> Someone sharper than me may come up with a one-formula solution, but
>> about
>> the best I can do is recommend using a helper column.
>>
>> In the helper column (assume you have 1st entry, like asd, in A1) on row
>> 1,
>> put this formula:
>> =COUNT(1*MID(A1,ROW($1:$9),1))
>> and 'commit' (end) the formula by pressing the [Shift]+[Ctrl]+[Enter] key
>> to
>> make it an array formula. Fill it down as far as required. make the $9
>> =
>> the longest possible string in your list that it may find. This formula
>> will
>> return the number of numeric characters found in each string. Now you
>> can
>> use this formula to get a count of entries in A that numbers in them
>> (this
>> assumes that your helper column with the array formula from above is in
>> column X)
>> =COUNTIF(X:X,">0")
>>
>> Hope that helps.
>>
>> "pmartglass" wrote:
>>
>> > That works great if the number is at the beginning of the string, is
>> > there a
>> > way to do it if the number is anyplace in the string ?
>> >
>> > "JLatham" wrote:
>> >
>> > > If all of the ones with number have a number as the 1st character,
>> > > then a
>> > > formula like this should work, just change the A1:A8 range to
>> > > whatever range
>> > > you need:
>> > > =COUNTIF(A1:A8,"<A")
>> > >
>> > >
>> > > "Aris" wrote:
>> > >
>> > > > Hi,
>> > > >
>> > > > I'm just trying to count the cells in a range that have
>> > > > alphanumeric data.
>> > > >
>> > > > example: asd, we, rty, 22r, 45aa, 1s, wer, tyu.
>> > > >
>> > > > I need to count all cells in that range that have numbers.

Hmmm...

Well, that formula ASSUMES the data to be tested is *alphanumeric*. If there
might be numbers in the cells:

ASD
BX0
10
100
1DF

Then add a COUNT function to the end:

=SUMPRODUCT(--(MMULT(COUNTIF(OFFSET(A1:A10,ROW(A1:A10)-1,,1),"*"&Nums&"*"),Array)>0))+COUNT(A1:A10)

--
Biff
Microsoft Excel MVP


"T. Valko" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> >is there a way to do it if the number is anyplace in the string ?
>
> I searched through my extensive formula library and I was surprised to
> learn that I had nothing for this.
>
> So, I whipped up this formula but it seems overly complex for the task at
> hand.
>
> Create these defined names:
>
> Nums
> Refers to:
> ={0,1,2,3,4,5,6,7,8,9}
>
> Array
> Refers to:
> ={1;1;1;1;1;1;1;1;1;1}
>
> Then:
>
> =SUMPRODUCT(--(MMULT(COUNTIF(OFFSET(A1:A10,ROW(A1:A10)-1,,1),"*"&Nums&"*"),Array)>0))
>
> Possible drawback: Depending on what version of Excel you're using the
> MMULT function is limited in range size. Versions prior to Excel 2007 are
> limited to no more than 5461 rows of data. No limit in Excel 2007.
>
> I'm reluctant to use helper columns/cells unless there is no other way but
> in this case I might even opt for the helpers.Well, maybe not!
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "pmartglass" <(E-Mail Removed)> wrote in message
> news:AFD66A17-0DF4-4DDA-A9E0-(E-Mail Removed)...
>> Thanks - every way I could think of required a helper column as well
>> thanks for the input
>>
>> "JLatham" wrote:
>>
>>> Someone sharper than me may come up with a one-formula solution, but
>>> about
>>> the best I can do is recommend using a helper column.
>>>
>>> In the helper column (assume you have 1st entry, like asd, in A1) on row
>>> 1,
>>> put this formula:
>>> =COUNT(1*MID(A1,ROW($1:$9),1))
>>> and 'commit' (end) the formula by pressing the [Shift]+[Ctrl]+[Enter]
>>> key to
>>> make it an array formula. Fill it down as far as required. make the $9
>>> =
>>> the longest possible string in your list that it may find. This formula
>>> will
>>> return the number of numeric characters found in each string. Now you
>>> can
>>> use this formula to get a count of entries in A that numbers in them
>>> (this
>>> assumes that your helper column with the array formula from above is in
>>> column X)
>>> =COUNTIF(X:X,">0")
>>>
>>> Hope that helps.
>>>
>>> "pmartglass" wrote:
>>>
>>> > That works great if the number is at the beginning of the string, is
>>> > there a
>>> > way to do it if the number is anyplace in the string ?
>>> >
>>> > "JLatham" wrote:
>>> >
>>> > > If all of the ones with number have a number as the 1st character,
>>> > > then a
>>> > > formula like this should work, just change the A1:A8 range to
>>> > > whatever range
>>> > > you need:
>>> > > =COUNTIF(A1:A8,"<A")
>>> > >
>>> > >
>>> > > "Aris" wrote:
>>> > >
>>> > > > Hi,
>>> > > >
>>> > > > I'm just trying to count the cells in a range that have
>>> > > > alphanumeric data.
>>> > > >
>>> > > > example: asd, we, rty, 22r, 45aa, 1s, wer, tyu.
>>> > > >
>>> > > > I need to count all cells in that range that have numbers.
>
>

Thanks a whole bunch. All these helped alot.

"T. Valko" wrote:

> Hmmm...
>
> Well, that formula ASSUMES the data to be tested is *alphanumeric*. If there
> might be numbers in the cells:
>
> ASD
> BX0
> 10
> 100
> 1DF
>
> Then add a COUNT function to the end:
>
> =SUMPRODUCT(--(MMULT(COUNTIF(OFFSET(A1:A10,ROW(A1:A10)-1,,1),"*"&Nums&"*"),Array)>0))+COUNT(A1:A10)
>
> --
> Biff
> Microsoft Excel MVP
>
>
> "T. Valko" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
> > >is there a way to do it if the number is anyplace in the string ?
> >
> > I searched through my extensive formula library and I was surprised to
> > learn that I had nothing for this.
> >
> > So, I whipped up this formula but it seems overly complex for the task at
> > hand.
> >
> > Create these defined names:
> >
> > Nums
> > Refers to:
> > ={0,1,2,3,4,5,6,7,8,9}
> >
> > Array
> > Refers to:
> > ={1;1;1;1;1;1;1;1;1;1}
> >
> > Then:
> >
> > =SUMPRODUCT(--(MMULT(COUNTIF(OFFSET(A1:A10,ROW(A1:A10)-1,,1),"*"&Nums&"*"),Array)>0))
> >
> > Possible drawback: Depending on what version of Excel you're using the
> > MMULT function is limited in range size. Versions prior to Excel 2007 are
> > limited to no more than 5461 rows of data. No limit in Excel 2007.
> >
> > I'm reluctant to use helper columns/cells unless there is no other way but
> > in this case I might even opt for the helpers.Well, maybe not!
> >
> > --
> > Biff
> > Microsoft Excel MVP
> >
> >
> > "pmartglass" <(E-Mail Removed)> wrote in message
> > news:AFD66A17-0DF4-4DDA-A9E0-(E-Mail Removed)...
> >> Thanks - every way I could think of required a helper column as well
> >> thanks for the input
> >>
> >> "JLatham" wrote:
> >>
> >>> Someone sharper than me may come up with a one-formula solution, but
> >>> about
> >>> the best I can do is recommend using a helper column.
> >>>
> >>> In the helper column (assume you have 1st entry, like asd, in A1) on row
> >>> 1,
> >>> put this formula:
> >>> =COUNT(1*MID(A1,ROW($1:$9),1))
> >>> and 'commit' (end) the formula by pressing the [Shift]+[Ctrl]+[Enter]
> >>> key to
> >>> make it an array formula. Fill it down as far as required. make the $9
> >>> =
> >>> the longest possible string in your list that it may find. This formula
> >>> will
> >>> return the number of numeric characters found in each string. Now you
> >>> can
> >>> use this formula to get a count of entries in A that numbers in them
> >>> (this
> >>> assumes that your helper column with the array formula from above is in
> >>> column X)
> >>> =COUNTIF(X:X,">0")
> >>>
> >>> Hope that helps.
> >>>
> >>> "pmartglass" wrote:
> >>>
> >>> > That works great if the number is at the beginning of the string, is
> >>> > there a
> >>> > way to do it if the number is anyplace in the string ?
> >>> >
> >>> > "JLatham" wrote:
> >>> >
> >>> > > If all of the ones with number have a number as the 1st character,
> >>> > > then a
> >>> > > formula like this should work, just change the A1:A8 range to
> >>> > > whatever range
> >>> > > you need:
> >>> > > =COUNTIF(A1:A8,"<A")
> >>> > >
> >>> > >
> >>> > > "Aris" wrote:
> >>> > >
> >>> > > > Hi,
> >>> > > >
> >>> > > > I'm just trying to count the cells in a range that have
> >>> > > > alphanumeric data.
> >>> > > >
> >>> > > > example: asd, we, rty, 22r, 45aa, 1s, wer, tyu.
> >>> > > >
> >>> > > > I need to count all cells in that range that have numbers.
> >
> >
>
>
> .
>

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Aris" <(E-Mail Removed)> wrote in message
news:1AABB67A-CFE8-4422-9404-(E-Mail Removed)...
> Thanks a whole bunch. All these helped alot.
>
> "T. Valko" wrote:
>
>> Hmmm...
>>
>> Well, that formula ASSUMES the data to be tested is *alphanumeric*. If
>> there
>> might be numbers in the cells:
>>
>> ASD
>> BX0
>> 10
>> 100
>> 1DF
>>
>> Then add a COUNT function to the end:
>>
>> =SUMPRODUCT(--(MMULT(COUNTIF(OFFSET(A1:A10,ROW(A1:A10)-1,,1),"*"&Nums&"*"),Array)>0))+COUNT(A1:A10)
>>
>> --
>> Biff
>> Microsoft Excel MVP
>>
>>
>> "T. Valko" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>> > >is there a way to do it if the number is anyplace in the string ?
>> >
>> > I searched through my extensive formula library and I was surprised to
>> > learn that I had nothing for this.
>> >
>> > So, I whipped up this formula but it seems overly complex for the task
>> > at
>> > hand.
>> >
>> > Create these defined names:
>> >
>> > Nums
>> > Refers to:
>> > ={0,1,2,3,4,5,6,7,8,9}
>> >
>> > Array
>> > Refers to:
>> > ={1;1;1;1;1;1;1;1;1;1}
>> >
>> > Then:
>> >
>> > =SUMPRODUCT(--(MMULT(COUNTIF(OFFSET(A1:A10,ROW(A1:A10)-1,,1),"*"&Nums&"*"),Array)>0))
>> >
>> > Possible drawback: Depending on what version of Excel you're using the
>> > MMULT function is limited in range size. Versions prior to Excel 2007
>> > are
>> > limited to no more than 5461 rows of data. No limit in Excel 2007.
>> >
>> > I'm reluctant to use helper columns/cells unless there is no other way
>> > but
>> > in this case I might even opt for the helpers.Well, maybe not!
>> >
>> > --
>> > Biff
>> > Microsoft Excel MVP
>> >
>> >
>> > "pmartglass" <(E-Mail Removed)> wrote in message
>> > news:AFD66A17-0DF4-4DDA-A9E0-(E-Mail Removed)...
>> >> Thanks - every way I could think of required a helper column as well
>> >> thanks for the input
>> >>
>> >> "JLatham" wrote:
>> >>
>> >>> Someone sharper than me may come up with a one-formula solution, but
>> >>> about
>> >>> the best I can do is recommend using a helper column.
>> >>>
>> >>> In the helper column (assume you have 1st entry, like asd, in A1) on
>> >>> row
>> >>> 1,
>> >>> put this formula:
>> >>> =COUNT(1*MID(A1,ROW($1:$9),1))
>> >>> and 'commit' (end) the formula by pressing the [Shift]+[Ctrl]+[Enter]
>> >>> key to
>> >>> make it an array formula. Fill it down as far as required. make the
>> >>> $9
>> >>> =
>> >>> the longest possible string in your list that it may find. This
>> >>> formula
>> >>> will
>> >>> return the number of numeric characters found in each string. Now
>> >>> you
>> >>> can
>> >>> use this formula to get a count of entries in A that numbers in them
>> >>> (this
>> >>> assumes that your helper column with the array formula from above is
>> >>> in
>> >>> column X)
>> >>> =COUNTIF(X:X,">0")
>> >>>
>> >>> Hope that helps.
>> >>>
>> >>> "pmartglass" wrote:
>> >>>
>> >>> > That works great if the number is at the beginning of the string,
>> >>> > is
>> >>> > there a
>> >>> > way to do it if the number is anyplace in the string ?
>> >>> >
>> >>> > "JLatham" wrote:
>> >>> >
>> >>> > > If all of the ones with number have a number as the 1st
>> >>> > > character,
>> >>> > > then a
>> >>> > > formula like this should work, just change the A1:A8 range to
>> >>> > > whatever range
>> >>> > > you need:
>> >>> > > =COUNTIF(A1:A8,"<A")
>> >>> > >
>> >>> > >
>> >>> > > "Aris" wrote:
>> >>> > >
>> >>> > > > Hi,
>> >>> > > >
>> >>> > > > I'm just trying to count the cells in a range that have
>> >>> > > > alphanumeric data.
>> >>> > > >
>> >>> > > > example: asd, we, rty, 22r, 45aa, 1s, wer, tyu.
>> >>> > > >
>> >>> > > > I need to count all cells in that range that have numbers.
>> >
>> >
>>
>>
>> .
>>