CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-2010excel-2007
Arshit patel
Arshit patel

Reputation: 133

How to count total numbers in specific cell

How can we count total numbers in one cell.

for e.g. I have 1422.45 in cell F11, Then count value should be 6 considering 1 4 2 2 4 5

here blanks,special character,text etc. should not be counted in other words only numbers should be counted.

This can be solved Either by formula or by vba.

any help ll be appriciated. thank you.

Upvotes: 0

Views: 115

Answers (2)

Tom Sharpe
Tom Sharpe

Reputation: 34370

You can also do it by formula:

=SUMPRODUCT(--ISNUMBER(FIND(MID(A1,ROW($A$1:INDEX($A:$A,LEN(A1))),1),"0123456789")))

Upvotes: 1

Jorge Ribeiro
Jorge Ribeiro

Reputation: 1138

Try this:

Public Function CountDigits(value As String)
CountDigits = 0

For x = 1 To Len(value)
Ext = Mid$(value, x, 1)
If InStr(1, "0123456789", Ext, vbTextCompare) > 0 Then
CountDigits = CountDigits + 1
End If
Next

End Function

Upvotes: 3

Related Questions