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,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
 |  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