Extract and sum numeric substrings in Excel

Question

I don't think you can do regular expressions natively in Excel and I get the impression this will require some VBScript, which I don't know.

I have a dataset which includes strings of the following format in a given column

123 foo, 234-bar, 345 baz , ...

I want to extract the numbers at the beginning of each comma-separated record and sum them in another column.

If we split the string on commas and consider substrings, I think this will suffice as a regex

^\D*?(\d+)?\D*/

(Only the first of multiple numbers should be considered and a numberless string should extract empty and be considered zero.)

Here is the expected output from some sample data

INPUT                          OUTPUT
-------------------------------------
5-foo, 10 bar                  15
11 baz,                        11
bad,string                     0
hello,123,100,   100   ,xxx    323

How do I make Excel perform this calculation?

check this link: http://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops — Dmitry Pavliv, Apr 04 '14 at 11:30

score 0 · Accepted Answer · edited May 23 '17 at 11:57

To use VBScript RegExps in Excel/VBA, add a Reference to "Microsoft VBScript Regular Expressions 5.5" (version may change) as described in detail by the answer simoco pointed to.

To sum sequences of digits from a string use a simple pattern like "\d+".

Evidence:

Option Explicit

Dim aTests : aTests = Array(_
    Array("5-foo, 10 bar", 15) _
  , Array("11 baz", 11) _
  , Array("bad,string", 0) _
  , Array("hello,123,100,   100   ,xxx", 323) _
)
Dim reNum : Set reNum = New RegExp
reNum.Global = True
reNum.Pattern = "\d+"
Dim aTest, nSum, oMTS, oMT
For Each aTest In aTests
    Set oMTS = reNum.Execute(aTest(0))
    nSum = 0
    For Each oMT In oMTS
        nSum = nSum + CLng(oMT.Value)
    Next
    WScript.Echo qq(aTest(0)), nSum, CStr(nSum = aTest(1))
Next

Function qq(s) : qq = """" & s & """" : End Function

output:

cscript 22861549.vbs
"5-foo, 10 bar" 15 True
"11 baz" 11 True
"bad,string" 0 True
"hello,123,100,   100   ,xxx" 323 True

(Regular expressions exist to make difficult things easy, not vice versa)

Extract and sum numeric substrings in Excel

1 Answers1