There are three types of cells in the following Excel sheet – cells containing single numbers, cells having multiple numbers separated by line break, and empty cells, as shown below:

Task: Extract all numbers and sum them. The expected result is 13.

Excel doesn’t have special functions for splitting numbers from a string, including one containing separators.

Directions of doing the task with esProc:

1. Start esProc (Download esProc installation package and free DSK edition license HERE. You will be prompted to load the license file when you run esProc for the first time).

2. Open Excel and load the add-in file ExcelRaq.xll through Option –> add-ins in Excel and click the Go button on the dialog. The add-in file is located in [esProc installation directory \bin]. Related information is easy to get if you don’t know how to load the add-in.

3. Enter the following formula in any cell, say A3, of the Excel sheet:

=esproc("=?.conj(string(~).words@d()).sum(int(~))",A1:D2)

Press carriage return to get the desired result. In the formula, words() function extracts specific members from a string. By default the function extracts words; it will extract numbers when working with @d option; the tilde symbol represents the current member of the set.