Count Occurrences Of Numbers & Letters In Single Cells

  • I have identification numbers and letters which go on telephone boxes.


    Like CVR 1-2-3-4-R
    DVR 5-6-7-8-9-11-L
    DVR-0-1R


    What I need is to find the total number of letters and numbers I need for the job.
    I did a find and replace with a blank which deletes that item and I write the number of deletions down. I continue until nothing is left. I need numbers 0 thru 9, letters A-Z and any other character like "-".


    Above example C=1,D=2,V=3,R=5 etc. My list is in Colulmn "A" and the count can be Column "B"


    Thanks

  • Re: Find And Count The Occurances Of Numbers And Letters


    See if the attached is of any help.


    Top row = sum of characters
    Row2 = header row of characters


    Formula cells: =LEN($A3)-LEN(SUBSTITUTE( UPPER($A3),B$2,""))

  • Re: Count Occurrences Of Numbers & Letters In Single Cells


    That gave me the start I needed. Attached is what I used by adding sumproduct to the formula.


    I put the Letters, Numbers and Characters in row "A"
    Data in range A3-A10


    =SUMPRODUCT(LEN($A3:$A10)-LEN(SUBSTITUTE( UPPER($A3:$A10),B$1,"")))



    Thanks for your usual support!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!