Count of numerical values within a cell

S

Sasikiran

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
 
S

Sasikiran

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
 
T

Teethless mama

=SUMPRODUCT(--ISNUMBER(VALUE((MID(A2,ROW($A$1:INDEX($A:$A,LEN(A2),1)),1)))))


You can simplify to the one below. It looks more elegant

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Ignore text but count values in same cell 2
How to use "count if" with criteria within a cell? 2
Controlling numerical values of option buttons in a group? 2
Count of unique values 8
Count specific characters in a cell 7
Excel Need Countifs Formula Help 0
Count of visible, non-blank cells only? 8
Deriving from 0.5" to 0.5 and so on ..... Values only. 2

Top