Counting words with numbers in them
 

Hi all,
If I have a row of words and numbers like this:

example
1922-example
anotherexample
3311
athirdexample
332

Is there a way to use the COUNTIF function to count how many of these words
are numbers, and how many start with numbers? So, in this example, 1 word
starts with a number, and 2 are numbers. Failing that, is there a way to
just count how many words have numbers in them, so there would be 3 in this
example? Thanks!

Counting words with numbers in them
 

Start with a number and the rest text


=SUMPRODUCT(--(ISNUMBER(--LEFT(A1:A10))),--(ISERR(--(A1:A10))))


pure numbers

=COUNT(A1:A10)


--

Regards,

Peo Sjoblom

Northwest Excel Solutions

www.nwexcelsolutions.com

(remove ^^ from email address)

Portland, Oregon




"jezzica85" wrote in message
...
Hi all,
If I have a row of words and numbers like this:

example
1922-example
anotherexample
3311
athirdexample
332

Is there a way to use the COUNTIF function to count how many of these
words
are numbers, and how many start with numbers? So, in this example, 1 word
starts with a number, and 2 are numbers. Failing that, is there a way to
just count how many words have numbers in them, so there would be 3 in
this
example? Thanks!

Counting words with numbers in them
 

On Fri, 10 Mar 2006 19:47:27 -0800, jezzica85
wrote:

Hi all,
If I have a row of words and numbers like this:

example
1922-example
anotherexample
3311
athirdexample
332

Is there a way to use the COUNTIF function to count how many of these words
are numbers, and how many start with numbers? So, in this example, 1 word
starts with a number, and 2 are numbers. Failing that, is there a way to
just count how many words have numbers in them, so there would be 3 in this
example? Thanks!

I can't do it with the COUNTIF function but:

Values that are Numbers:

=SUMPRODUCT(--ISNUMBER(rng))

Values that start with a Number (this includes the values that ARE numbers)

=SUMPRODUCT(--ISNUMBER(-LEFT(rng,1)))

Non-numeric values that start with a number would just be the difference of the
above two:

=SUMPRODUCT(--ISNUMBER(-LEFT(rng,1)))-SUMPRODUCT(--ISNUMBER(rng))
--ron

Counting words with numbers in them
 

Thank you both for the help!

"Ron Rosenfeld" wrote:

On Fri, 10 Mar 2006 19:47:27 -0800, jezzica85
wrote:

Hi all,
If I have a row of words and numbers like this:

example
1922-example
anotherexample
3311
athirdexample
332

Is there a way to use the COUNTIF function to count how many of these words
are numbers, and how many start with numbers? So, in this example, 1 word
starts with a number, and 2 are numbers. Failing that, is there a way to
just count how many words have numbers in them, so there would be 3 in this
example? Thanks!

I can't do it with the COUNTIF function but:

Values that are Numbers:

=SUMPRODUCT(--ISNUMBER(rng))

Values that start with a Number (this includes the values that ARE numbers)

=SUMPRODUCT(--ISNUMBER(-LEFT(rng,1)))

Non-numeric values that start with a number would just be the difference of the
above two:

=SUMPRODUCT(--ISNUMBER(-LEFT(rng,1)))-SUMPRODUCT(--ISNUMBER(rng))
--ron