Count the digits

[pratibha.sharma841]

Hi All,

I have a query.

Data in Cell A1 - 1,2,5

and

Data in Cell B1 - 2,7

I want to count the digits of both cells and Output required : - 5

Any solution for this.

Rgds

PS

[Ashish Bhalara]

Hi, try below UDF.

Function LenRng(Rng As Range) 'Give a cell range which you want to count.

Dim r As Range

For Each r In Rng

    LenRng = LenRng + Len(r)

Next r

End Function

Regards

Ashish Bhalara

--
www.ExcelVbaLab.com
---
You received this message because you are subscribed to the Google Groups "Excel VBA Lab" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ExcelVbaLab...@googlegroups.com.
To post to this group, send email to Excel...@googlegroups.com.
Visit this group at http://groups.google.com/group/ExcelVbaLab.
To view this discussion on the web visit https://groups.google.com/d/msgid/ExcelVbaLab/2e38ac35-3cd4-4def-8ce5-dc83f8150d02%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Pratibha Sharma]

Dear Bhalara,

Thanks for your help.

UDF which was shared by you is not giving proper output.

I need to count only digits and this UDF count's the digits with ","

kindly check.

Rgds

PS

[Ashish Bhalara]

Hi, see below UDF code which is count only numeric value in cell range and also see attached file for example.

Function CountNumeric(Rng As Range) As Integer

Dim R As Range

Dim i, j As Integer

For Each R In Rng

  For i = 1 To Len(R)

    For j = 48 To 57

        If Mid(R, i, 1) = Chr(j) Then

            CountNumeric = CountNumeric + 1

            Exit For

        End If

    Next j

  Next i

Next R

End Function

Regards

Ashish Bhalara

To view this discussion on the web visit https://groups.google.com/d/msgid/ExcelVbaLab/5c3e1a77-7707-48eb-a917-18f0d16e5ffc%40googlegroups.com.

[Pratibha Sharma]

Dear Mr. Bhalara,

It's working well :)

But have some issues in the UDF which is shared by you.

If I put Data in A1 - 1,3,5

and

Put Data in B1 - 4,5

then It will giving proper output which is - 5

But the issue is arrive when I put Data in A1 - 1,3,5

and

Put Data in B1 - 33,5,7

Then it will giving wrong output according to the requirement.

Wrong Output is giving by UDF - 7

Correct Output is - 6

Can you please check it.

Thanks for your help.

Rgds

PS 

[Ashish Bhalara]

Hi Sharmaji, you have not explain more that is any value include in range containing alphabetic or other special character, so I assume that only coma is there and split value by coma (,) and count it. Try below UDF.

Function CountVal(Rng As Range) As Integer

Dim R As Range

Dim i, j As Integer

For Each R In Rng

    For i = 1 To Len(R)

        If Mid(R, i, 1) = Chr(44) Then

        CountVal = CountVal + 1

        End If

    Next i

Next R

CountVal = CountVal + Application.WorksheetFunction.CountA(Rng)

End Function

Regards

Ashish Bhalara

--

www.ExcelVbaLab.com
---
You received this message because you are subscribed to the Google Groups "Excel VBA Lab" group.

To unsubscribe from this group and stop receiving emails from it, send an email to ExcelVbaLab...@googlegroups.com.
To post to this group, send email to Excel...@googlegroups.com.
Visit this group at http://groups.google.com/group/ExcelVbaLab.

To view this discussion on the web visit https://groups.google.com/d/msgid/ExcelVbaLab/2e4f09eb-9aa3-474f-b897-9afc7b71851d%40googlegroups.com.

[Pratibha Sharma]

Dear Mr. Bhalara,

It's working as per my requirement. 

Work well :)

Thanks a ton....!

Rgds

PS

[Ashish Kumar]

Dear Ashish Bhalara,

Excellent Solution.

Great work.

Regards

Ashish Kumar