Excel – Counting letters and numbers separately in a single cell

Accepted answer +5 upvotes

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},"")))