Prev: each cell have same number of charaters
Next: How can I restore a column that was deleted by mistake
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,6724405,,,,//1,804,6724405,,,, = 22 1,,201.368.5041,,,,//1,,201.368.5041,,,, = 22 1,503,6014108 11 Thanks
From: Sasikiran on 6 May 2010 09:09 Sorry messed up with the example. A1 B1 Desired Result 1,804,6724405,,,,//1,804,6724405,,,, = 22 1,,201.368.5041,,,,//1,,201.368.5041,,,, = 22 1,503,6014108 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,6724405,,,,//1,804,6724405,,,, = > 22 > 1,,201.368.5041,,,,//1,,201.368.5041,,,, = > 22 > 1,503,6014108 > 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,6724405,,,,//1,804,6724405,,,, = > 22 > 1,,201.368.5041,,,,//1,,201.368.5041,,,, = > 22 > 1,503,6014108 > 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,6724405,,,,//1,804,6724405,,,, = > 22 > 1,,201.368.5041,,,,//1,,201.368.5041,,,, = > 22 > 1,503,6014108 > 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,6724405,,,,//1,804,6724405,,,, = > > 22 > > 1,,201.368.5041,,,,//1,,201.368.5041,,,, = > > 22 > > 1,503,6014108 > > 11 > > > > Thanks

Next

Last
Pages: 1 2 Prev: each cell have same number of charaters Next: How can I restore a column that was deleted by mistake 