| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| count 2 criteria text or numbers | Excel Worksheet Functions | |||
| Count, numbers and text | Excel Discussion (Misc queries) | |||
| How do i count numbers and letters to find a total count of all | Excel Worksheet Functions | |||
| Number count - postcodes and need to count the numbers of tim... | Excel Discussion (Misc queries) | |||
| count a group of numbers but do not count duplicates | Excel Worksheet Functions | |||
Linear Mode
