CODEWITHSUNDEEP
excelexcel-formulavba
CvR
CvR

Reputation: 63

Excel - Counting letters and numbers separately in a single cell

I need a way to count numbers and letters separately within one cell.

For example, if a cell contains 1234567ABC I need to be able to output this as

I can't think of a way to use the len() function that would work, and countif only counts the cells themselves.

Any help would be appreciated.

Upvotes: 6

Views: 57434

Answers (2)

brettdj
brettdj

Reputation: 55682

From my answer in Analyse format of alpha-numeric string:

For a more detailed answer this string 1234567ABC456 would be reported as 7N3L3N

A regexp like this will do the job

  • press together to go the VBE
  • Insert Module
  • copy and paste the code below
  • press together to go back to Excel

then you can use the function (which also detects invalid strings) within Excel, ie in B1
=AlphaNumeric(A1)

enter image description here

Function AlphaNumeric(strIn As String) As String
    Dim objRegex As Object
    Dim objRegMC As Object
    Dim objRegM As Object
    Dim strOut As String
    Set objRegex = CreateObject("vbscript.regexp")
    With objRegex
        .Global = True
        .ignorecase = True
        .Pattern = "[^\w]"
        If .test(strIn) Then
            AlphaNumeric = "One or more characters is invalid"
        Else
            .Pattern = "(\d+|[a-z]+)"
            Set objRegMC = .Execute(strIn)
            For Each objRegM In objRegMC
                strOut = strOut & (objRegM.Length & IIf(IsNumeric(objRegM), "N", "L"))
            Next
            AlphaNumeric = strOut
        End If
    End With
End Function

Upvotes: 3

danielpiestrak
danielpiestrak

Reputation: 5439

If each cell is filled only with numbers and letters, a quick non-vba way to accomplish this is to nest a substitute function 10 times to remove the 10 numerical characters. what you're left with is alpha only. Then you can len() the alpha text / subtract that number from the original length to get the numerical length.

Assuming "1234567ABC" is in cell A1:

This formula gives the number of letters. (3)

=LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))

This formula gives the total numbers: (7)

=LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))

If you want to start handling the data in other ways / any more in depth, a VBA solution will likely be required.

Note

To meet requirements in your original post, add this suffix to the end of the above formulas: 

=x & " Numbers / Letters"

Where x = the above two formulas. this will add the text after the calculated number.

Further Reading:

The following link details a VBA UDF that does something similar: http://www.mrexcel.com/forum/excel-questions/16364-how-remove-numbers.html

Additional Update (thanks lori_m)

This formula is a LOT easier to read / update:

=SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9,0},"")))

Upvotes: 8

Related Questions