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

Would like to input 3 letters into a cell, each having a different numerical value 5
countif text values to represent different numerical values 5
how to find text vaues for a given grade? 0
How can count the number of cells that fulfill certain criteria in other cells 2
Assign numerical value to cell based on selection from drop-down m 1
Ignore text but count values in same cell 3
Getting text or numerical data from the formula 2
check first 3 characters of a cell and compare to a table 4

Top