From: Sasikiran on 6 May 2010 09:00 Dear, I am looking for a formula which counts only the numerical values within a cell ignoring the special characters, text and anything else and gives the result in column B using a formula. Below is the example. A1 B1 Desired Result 1,804,672-4405,,,,//1,804,672-4405,,,, = 22 1,,201.368.5041,,,,//1,,201.368.5041,,,, = 22 1,503,601-4108 11 Thanks From: Sasikiran on 6 May 2010 09:09 Sorry messed up with the example. A1 B1 Desired Result 1,804,672-4405,,,,//1,804,672-4405,,,, = 22 1,,201.368.5041,,,,//1,,201.368.5041,,,, = 22 1,503,601-4108 11 "Sasikiran" wrote: > Dear, > > I am looking for a formula which counts only the numerical values within a > cell ignoring the special characters, text and anything else and gives the > result in column B using a formula. > > Below is the example. > > A1 B1 > Desired Result > 1,804,672-4405,,,,//1,804,672-4405,,,, = > 22 > 1,,201.368.5041,,,,//1,,201.368.5041,,,, = > 22 > 1,503,601-4108 > 11 > > Thanks From: Brad on 6 May 2010 09:11 =SUMPRODUCT(--ISNUMBER(VALUE((MID(A2,ROW(\$A\$1:INDEX(\$A:\$A,LEN(A2),1)),1))))) -- Wag more, bark less "Sasikiran" wrote: > Dear, > > I am looking for a formula which counts only the numerical values within a > cell ignoring the special characters, text and anything else and gives the > result in column B using a formula. > > Below is the example. > > A1 B1 > Desired Result > 1,804,672-4405,,,,//1,804,672-4405,,,, = > 22 > 1,,201.368.5041,,,,//1,,201.368.5041,,,, = > 22 > 1,503,601-4108 > 11 > > Thanks From: Teethless mama on 6 May 2010 10:02 =SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))) "Sasikiran" wrote: > Dear, > > I am looking for a formula which counts only the numerical values within a > cell ignoring the special characters, text and anything else and gives the > result in column B using a formula. > > Below is the example. > > A1 B1 > Desired Result > 1,804,672-4405,,,,//1,804,672-4405,,,, = > 22 > 1,,201.368.5041,,,,//1,,201.368.5041,,,, = > 22 > 1,503,601-4108 > 11 > > Thanks From: Brad on 6 May 2010 10:14 Your formula is better -- Wag more, bark less "Teethless mama" wrote: > =SUMPRODUCT(LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))) > > "Sasikiran" wrote: > > > Dear, > > > > I am looking for a formula which counts only the numerical values within a > > cell ignoring the special characters, text and anything else and gives the > > result in column B using a formula. > > > > Below is the example. > > > > A1 B1 > > Desired Result > > 1,804,672-4405,,,,//1,804,672-4405,,,, = > > 22 > > 1,,201.368.5041,,,,//1,,201.368.5041,,,, = > > 22 > > 1,503,601-4108 > > 11 > > > > Thanks