Excel - counting letters and numbers separately in one cell

Question

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

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

• "7 rooms" and
• "3 letters".

I cannot think of a way to use the len() function, which will work, and countif will only count the cells themselves.

Any help would be appreciated.

Answer 1

If each cell is filled only with numbers and letters, a quick non-vba way to do this is to decompose the substitute function 10 times to remove 10 numeric characters. what you have left is only alpha. Then you can len() alpha text / subtract this number from the original length to get the number 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 number: (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 processing the data in other ways / in more detail, you may need a VBA solution.

Note

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

 =x & " Numbers / Letters" 

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

Further reading:

The following link points to VBA UDF, which does something similar: http://www.mrexcel.com/forum/excel-questions/16364-how-remove-numbers.html

Additional update (thanks lori_m)

This LOT formula is easier to read / update:

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

Answer 2

From my answer in Analyze the format of an alphanumeric string :

For a more detailed answer, this line 1234567ABC456 will be reported as 7N3L3N

A regular expression like this will do the job

• press alt f11 together to go to VBE
• Insert module
• copy and paste the code below
• press alt f11 together to return to Excel

then you can use the function (which also detects invalid rows) in Excel, i.e. in B1
=AlphaNumeric(A1)

 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+|[az]+)" 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