Count numbers within text
 

I am trying to get a formula that will count the number of numbers within a
text string.

Example

320,J,KLY - i would like the function to return 3
,32,T,J,KLYU - I would like the function to return 2
1234,h,KlY - i am looking for 4

Anyone know if this is possible?

Thanks

Count numbers within text
 

Try this:

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(INDIRECT("48:57"))),"") ))

--
Biff
Microsoft Excel MVP


"LiAD" wrote in message
...
I am trying to get a formula that will count the number of numbers within a
text string.

Example

320,J,KLY - i would like the function to return 3
,32,T,J,KLYU - I would like the function to return 2
1234,h,KlY - i am looking for 4

Anyone know if this is possible?

Thanks

Count numbers within text
 

outstanding, works a treat

thanks a million

"T. Valko" wrote:

Try this:

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(INDIRECT("48:57"))),"") ))

--
Biff
Microsoft Excel MVP


"LiAD" wrote in message
...
I am trying to get a formula that will count the number of numbers within a
text string.

Example

320,J,KLY - i would like the function to return 3
,32,T,J,KLYU - I would like the function to return 2
1234,h,KlY - i am looking for 4

Anyone know if this is possible?

Thanks

Count numbers within text
 

=SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))


"LiAD" wrote:

I am trying to get a formula that will count the number of numbers within a
text string.

Example

320,J,KLY - i would like the function to return 3
,32,T,J,KLYU - I would like the function to return 2
1234,h,KlY - i am looking for 4

Anyone know if this is possible?

Thanks

Count numbers within text
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"LiAD" wrote in message
...
outstanding, works a treat

thanks a million

"T. Valko" wrote:

Try this:

=SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(ROW(INDIRECT("48:57"))),"") ))

--
Biff
Microsoft Excel MVP


"LiAD" wrote in message
...
I am trying to get a formula that will count the number of numbers
within a
text string.

Example

320,J,KLY - i would like the function to return 3
,32,T,J,KLYU - I would like the function to return 2
1234,h,KlY - i am looking for 4

Anyone know if this is possible?

Thanks

Count numbers within text
 

Here is a similar version without the volatile function...

=SUMPRODUCT(--ISNUMBER(--MID(A1,ROW($1:$999),1)))

It will work as long as the cell contains less than 1000 characters.

--
Rick (MVP - Excel)


"Teethless mama" wrote in message
...
=SUMPRODUCT(--ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)))


"LiAD" wrote:

I am trying to get a formula that will count the number of numbers within
a
text string.

Example

320,J,KLY - i would like the function to return 3
,32,T,J,KLYU - I would like the function to return 2
1234,h,KlY - i am looking for 4

Anyone know if this is possible?

Thanks