Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

  • "7 Numbers" and
  • "3 Letters".

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.

like image 965
CvR Avatar asked Aug 29 '12 21:08

CvR


People also ask

How do I count letters and numbers in Excel?

To use the function, enter =LEN(cell) in the formula bar and press Enter. In these examples, cell is the cell you want to count, such as B1. To count the characters in more than one cell, enter the formula, and then copy and paste the formula to other cells.

How do I count specific text in a single cell in Excel?

Formula to Count Specific Text String This formula is entered in cell C5, and copied down to cell C8: =(LEN(B5) - LEN(SUBSTITUTE(B5, $B$2,""))) / LEN($B$2)


2 Answers

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},"")))
like image 141
danielpiestrak Avatar answered Oct 28 '22 12:10

danielpiestrak


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 altf11 together to go the VBE
  • Insert Module
  • copy and paste the code below
  • press altf11 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
like image 41
brettdj Avatar answered Oct 28 '22 11:10

brettdj