Excel:对单元格中文本块内的数字求和 [英] Excel: Sum numbers inside a text block in a cell

问题描述

让我们说一个单元格中有一些文本读取

Lets say there is some text in a cell that reads

这是一个文本块(.7)，在此文本块(1.2)中有数字(2.5)，并且这些数字需要加在一起(.4)"

"this is a block of text (.7) and in this block of text (1.2) there are numbers (2.5) and these numbers need to be added together (.4)"

所有这些数字之和的答案将是.7 + 1.2 + 2.5 + .4 = 4.8

The answer to the sum of all these numbers would be .7+1.2+2.5+.4= 4.8

我的问题是，有没有一种方法可以让excel从一块文本中将所有数字加在一起，然后输出答案?它将始终是数字的总和，并且数字将始终为正.数字的数量会有所不同，可能是2，也可能是15，可能是任何数字.

My question is, is there a way that I can have excel add all the numbers together from a block of text and just output the answer? It will always be the sum of the numbers and the numbers will always be positive. The amount of numbers will vary, it could be 2 it could be 15, could be anything.

到目前为止，我已经尝试过:我已经尝试过"= sum"并突出显示始终给出答案"0"的整个单元格

What I have tried so far: I've tried "=sum" and highlighting the entire cell which always gives the answer "0"

推荐答案

这是一个使用正则表达式的UDF，它将仅添加括号内的那些值:

Here is a UDF using Regular Expressions which will add only those values that are within parentheses:

Option Explicit
Function sumNumsInParenth(S As String) As Double
    Dim RE As Object, MC As Object, M As Object
    Dim dSum As Double

Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .Pattern = "\((\d*(?:\.\d+)?)\)"
    If .test(S) = True Then
        Set MC = .Execute(S)
        For Each M In MC
            dSum = dSum + M.submatches(0)
        Next M
    End If
End With

sumNumsInParenth = dSum

End Function

正则表达式模式的说明

捕获括号内的浮点数，整数部分可选

capture floating point numbers within parentheses, integer portion optional

\((\d*(?:\.\d+)?)\)

选项:不区分大小写； ^ $匹配行间的中断

Options: Case insensitive; ^$ match at line breaks

• 匹配开头的括号字符 \(
• 匹配以下正则表达式并将其匹配项捕获到反向引用编号1中 (\d*(?:\.\d+)?)
  • 匹配单个字符，即数字" \d*
    • 在零次至无限次之间进行尽可能多的次数，并根据需要进行回馈(贪婪) *
    • Match the opening parenthesis character \(
    • Match the regex below and capture its match into backreference number 1 (\d*(?:\.\d+)?)
      • Match a single character that is a "digit" \d*
        • Between zero and unlimited times, as many times as possible, giving back as needed (greedy) *
        • 在零到一遍之间，尽可能多地遍历，并根据需要进行回馈(贪婪) ?
        • 匹配字符."从字面上看 \.
        • 匹配单个字符，即数字" \d+
          • 在一次和无限次之间进行尽可能多的次数，并根据需要进行回馈(贪婪) +
          • Between zero and one times, as many times as possible, giving back as needed (greedy) ?
          • Match the character "." literally \.
          • Match a single character that is a "digit" \d+
            • Between one and unlimited times, as many times as possible, giving back as needed (greedy) +

            使用 RegexBuddy

            这篇关于Excel:对单元格中文本块内的数字求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！