vba Excel - 在单个单元格中分别计算字母和数字

Excel - Counting letters and numbers separately in a single cell

提问by CvR

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

我需要一种方法来分别计算一个单元格中的数字和字母。

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

例如，如果一个单元格包含1234567ABC我需要能够将其输出为

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

• “7个数字”和
• “三个字母”。

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

我想不出一种使用该len()功能的方法，countif只能计算单元格本身。

Any help would be appreciated.

任何帮助，将不胜感激。

回答by danielpiestrak

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.

如果每个单元格仅填充数字和字母，则实现此目的的一种快速非 vba 方法是嵌套替代函数 10 次以移除 10 个数字字符。你只剩下阿尔法。然后，您可以len()将 alpha 文本/从原始长度中减去该数字以获得数字长度。

Assuming "1234567ABC" is in cell A1:

假设“1234567ABC”在单元格 A1 中：

This formula gives the number of letters. (3)

这个公式给出了字母的数量。(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)

这个公式给出了总数： (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.

如果您想开始以其他方式/更深入地处理数据，则可能需要 VBA 解决方案。

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.

其中 x = 以上两个公式。这将在计算出的数字后添加文本。

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

以下链接详细介绍了执行类似操作的 VBA UDF：http: //www.mrexcel.com/forum/excel-questions/16364-how-remove-numbers.html

Additional Update(thanks lori_m)

附加更新（感谢 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},"")))

回答by brettdj

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

从我在分析字母数字字符串格式的回答中：

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

对于更详细的答案，此字符串 1234567ABC456将报告为 7N3L3N

A regexp like this will do the job

像这样的正则表达式将完成这项工作

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

• 同时按alt f11进入 VBE
• 插入模块
• 复制并粘贴下面的代码
• 同时按alt f11返回 Excel

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

然后您可以在 Excel 中使用该函数（它还检测无效字符串），即在 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+|[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