提示:其它语言是由 Google 机器翻译的。 你可以访问 English 版本。
登录
x
or
x
x
马上登记
x

or

如何计算单元格中的字符,字母和数字的数量?

当您在Excel中的单元格中键入数据列表时(如下面的屏幕截图所示),您想要统计所有字符的总数,或者只计算字母的数量,或者只计算单元格中的数字。 现在,我在Excel中讨论这个计数的方法。


计数单词出现在Excel单元格中

如果一个单词在需要计数的单元格中多次出现,通常,您可以逐个计算它们。 但是,如果这个词出现了数百次,那么手动计数很麻烦。 该 计数时间出现一个词 功能 Kutools for Excel's 配方助手 group可以快速计算单词出现在单元格中的次数。 在60天免费试用全功能!
doc count一个字
Kutools for Excel:拥有超过300便利的Excel加载项,可以在60天免费试用,不受限制。

如果要计算每个单元格中所有字符的总数(包括数字,字母和其他标记),请按以下方式进行:

1。 键入此公式 = LEN(A1) (Cell A1表示要计算总字符的单元格)放入空白单元格中,例如单元格B1,然后单击 输入 按钮,并计算了单元格A1中的字符总数。 看截图:

2。 拖动填充句柄以将此公式应用于范围单元格,并且列表中每个单元格中的字符数都已计算在内。 看截图:


如果您只希望每个单元格中的数字不包括数字,则可以按照以下方式进行操作:

选择一个空白单元格,例如单元格B1,键入此公式 = LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,""))
(Cell A1表示您想要计算除数字外的字母数量的单元格,您可以根据需要更改它),然后按 输入 并拖动填充手柄以填充要使用此公式的范围。 看截图:


选择一个空白单元格,例如单元格B1,键入此公式 = SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9,0}))) (Cell A1指示您只需计算数量的单元格,您可以根据需要更改它),然后按 输入 并拖动填充手柄以填充要使用此公式的范围。 看截图:


使用此功能,您不仅可以知道单元格字符串中有多少个字母或数字,还可以知道字母和数字的顺序。

1。 保持 ALT 按钮并按下 F11 在键盘上打开一个 Microsoft Visual Basic for Application 窗口。

2。 点击 插页 > 模块,并将VBA复制到模块中。

VBA:用函数计算字母和数字的数量

Function AlphaNumeric(pInput As String) As String
'Updateby20140303
Dim xRegex As Object
Dim xMc As Object
Dim xM As Object
Dim xOut As String
Set xRegex = CreateObject("vbscript.regexp")
xRegex.Global = True
xRegex.ignorecase = True
xRegex.Pattern = "[^\w]"
AlphaNumeric = ""
If Not xRegex.test(pInput) Then
    xRegex.Pattern = "(\d+|[a-z]+)"
    Set xMc = xRegex.Execute(pInput)
    For Each xM In xMc
        xOut = xOut & (xM.Length & IIf(IsNumeric(xM), "N", "L"))
    Next
    AlphaNumeric = xOut
End If
End Function

3。 保存代码并关闭窗口,然后输入此公式 =个字母数字(A1) (细胞A1表示您想要计数的细胞,您可以根据需要将其更改)放入空白细胞,然后按 输入 并拖动填充手柄以填充要使用此公式的范围。 看截图:

提示:

(1)“L”表示字母,“N”表示数字。

(2)此函数不适用于包含特殊标记的单元格,如!,@,#,$,%,^,&等。


如果要计算字符串中特定字符的数量,例如,在字符串“我要计算字符串中特定的数字”中,我想要计算字符数“n”,如何可以你做?

在这种情况下,我介绍一下 Kutools for ExcelCOUNTCHAR 功能为你。

Kutools for Excel, 与超过 300 方便的功能,让您的工作更轻松。

安装后 Kutools for Excel,请按照以下步骤操作:(免费下载Kutools for Excel!)

1。 输入您想要在单元格中计数的字符,请参阅屏幕截图:
doc countchar 1

2。 然后选择一个空白单元格放置结果,并选择一个空白单元格将计数结果,然后单击 Kutools > Kutools函数 > 统计与数学 > COUNTCHAR。 看截图:
doc kutools 1

3。 然后在弹出 函数参数 对话框中,选择字符串 within_text中 框,并选择字符单元格放入 查找_ 框。 然后您可以看到对话框中出现计数结果。
doc countchar 3

4。 点击 OK,现在结果放入您选择的单元格中。

doc countchar 4

在Kutools函数中,您可以通过背景或字体颜色对数据进行计数,可以通过相同的背景或字体颜色对值进行求和,也可以将时间转换为十进制小时/分钟/秒等等。



excel单元格中的和数

有时,单元格中需要将一系列数字相加在一起,例如,923149,sum是28。 在Excel中,除了手数之外,没有内置函数。 然而 太阳数字在一个单元格中 功能 Kutools for Excel's 配方助手 group可以快速计算单元格中的总数。 60-day免费试用,功能齐全!
doc单元格中的总和数字
Kutools for Excel:拥有超过300便利的Excel加载项,可以在60天免费试用,不受限制。

相关文章:

  • 将文本字符串更改为句子
  • 将文本字符串更改为大写

  • Kutools for Excel - 最佳办公生产力工具提高80%的生产力

    • 重用: 快速插入 复杂的公式,图表 以及你以前用过的任何东西; 加密单元格 密码; 创建邮件列表 并发送电子邮件...
    • 超级方程式酒吧 (轻松编辑多行文字和公式); 阅读布局 (轻松读取和编辑大量单元格); 粘贴到过滤范围...
    • 合并单元格/行/列 不丢失数据; 分裂细胞含量; 组合重复的行/列...防止重复的细胞; 比较范围...
    • 选择复制或唯一 行; 选择空行 (所有细胞都是空的); 超级查找和模糊查找 在许多工作簿中; 随机选择......
    • 精确复制 多个单元格而不更改公式参考; 自动创建参考 多张表; 插入项目符号,复选框等等......
    • 提取文本,添加文本,按位置删除, 删除空间; 创建和打印分页小计; 在单元格内容和注释之间转换...
    • 超级过滤器 (将过滤方案保存并应用到其他工作表); 高级排序 按月/周/日,频率等; 特殊过滤器 用粗体,斜体......
    • 结合工作簿和工作表; 根据键列合并表; 将数据拆分为多个表格; 批量转换xls,xlsx和PDF...
    • 超过300强大的功能。 支持Office / Excel 2007-2019和365。 支持所有语言。 在您的企业或组织中轻松部署。 全功能60天免费试用。
    kte tab 201905

    Office选项卡为Office提供选项卡式界面,使您的工作更轻松

    • 在Word,Excel,PowerPoint中启用选项卡式编辑和阅读,Publisher,Access,Visio和Project。
    • 在同一窗口的新选项卡中打开并创建多个文档,而不是在新窗口中。
    • 通过50%提高您的工作效率,每天为您减少数百次鼠标点击!
    官方底部
    Say something here...
    symbols left.
    You are guest ( Sign Up? )
    or post as a guest, but your post won't be published automatically.
    Loading comment... The comment will be refreshed after 00:00.
    • To post as a guest, your comment is unpublished.
      vivek kachhadiya · 2 months ago
      Hi excel experts,
      Count only numbers within a cell, excluding letters & other characters
      So, let's say we have 1, 4, 5, 6. I'd like A2 to show "4".

      Formula used =SUM(LEN(X4)-LEN(SUBSTITUTE(X4,{1,2,3,4,5,6,7,8,9,0},))) so, Will be single digit OK. but two digit wrong formula.

      But Double digit number how to count...?

      so any double digit number, e.g., 10
    • To post as a guest, your comment is unpublished.
      David · 6 months ago
      Hi excel experts, is there some easy coding to count characters how often characters come in line. for example if I have the following line:
      ABBABBCCCBCCBCCCBB and I want to know how many times there are three (3) C in a row? in this case the correct answer is 2 times. But I would like to write a code to counts for me. It is possible?
      • To post as a guest, your comment is unpublished.
        Sunny · 6 months ago
        Sorry, I do not have any tricks on solving this job.
        • To post as a guest, your comment is unpublished.
          Asko Havumäki · 1 months ago
          Put string ABBABBCCCBCCBCCCBB to A1 and string CCC to B1. Now put fthis formula to C1: =(LEN(A1)-LEN(SUBSTITUTE(A1;B1;"")))/LEN(B1)
    • To post as a guest, your comment is unpublished.
      Vikas · 11 months ago
      hi need a help eg: =+1+2+3+4+5+6 now i want how many numbers in a common cell . Is that possible .Still i am not able to find any solution m keep searching from last 4 months . May Be you provide a better solution
      • To post as a guest, your comment is unpublished.
        Sunny · 10 months ago
        You can add' in the front of =1+2+3+4+5+6 to convert the formula to text, then apply Kutools for Excel's Remove by character to remove all characters except numbers, and use =LEN() to count the number.
    • To post as a guest, your comment is unpublished.
      patrick · 1 years ago
      Thankyou very much :) you helped me
    • To post as a guest, your comment is unpublished.
      Alex · 1 years ago
      Hi, i need to count numbers situated between characters, like in the example bellow, help me please.

      example: 1,2,3,4,5,67,89,8657,6789,23456,10

      i have 11 numbers situated between comma, is there a formula for this type of counting?

      Thank you.
    • To post as a guest, your comment is unpublished.
      Ivan Kesaulya · 1 years ago
      Hi excel experts, can you help me?
      I need to count previous numbers before special characters appeared.


      123456789|


      Could you help me?


      Thank you.
      • To post as a guest, your comment is unpublished.
        Sunny · 1 years ago
        Your question is a little complex, I cannot find a formula to solve it. But you can handle it like these:

        1. Extract all characters before the special character =LEFT(A1,FIND(",",A1)-1), the comma is the special character

        2. Then use the formula =LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
        (A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")) to count the numbers only.
    • To post as a guest, your comment is unpublished.
      Michael (Micky) Avid · 1 years ago
      To my opinion, the suggested formula for counting the amount of only digits with LEN function can be tremendously shorten !
      =SUM(LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9,0},)))
      ----------------------------
      Michael (Micky) Avidan
      “Microsoft® Answers" - Wiki author & Forums Moderator
      “Microsoft®” Excel MVP – Excel (2009-2018)
      ISRAEL.
    • To post as a guest, your comment is unpublished.
      Smyle · 2 years ago
      Dear Experts, Please guide.

      Product Code Qty/Box
      Pencil 51AB 10
      G. Total 10
      I have received purchase of product " Pencil " with Code 51AB, but i have posted in my system in OLD code which is 41AB. Now company system stock reports shows 10 Box with new Code ( 51AB ), but our internal system stock report showing stock in OLD code 41AB.
      what is the formula that i calculate these 2 different code in one code either it is new or old code.

      Kindly help out.
      Thanks
    • To post as a guest, your comment is unpublished.
      Stephen · 2 years ago
      Hi this is stephen,

      I have a Query in excel in one cell contains alpha and Numbers like cell 1: Name(123) cell 2 : Name (456) how count as a 2 (Numbers) in two different cells

      Like (123) =1 and (456)=2
      • To post as a guest, your comment is unpublished.
        Pallavi Shankar · 2 years ago
        Hello Am Pallavi here,

        Even i wanted to know the formula for the above one. Kindly help Us please