In the current version of Excel, the easiest way to solve this problem is with the TEXTSPLIT function.
It is possible to sum numbers separated by other delimiters as well.
See below for an example.
TEXTSPLIT function
TheTEXTSPLIT functionis designed to split a text string by a given delimiter into multiple values.
The minimal generic syntax for TEXTSPLIT looks like this:
The result from TEXTSPLIT is anarrayof separate values.
To do this, we use the VALUE function.
VALUE function
TheVALUE functionconverts text that appears in a recognized format (i.e.
a number, date, or time format) into a numeric value.
As a bonus, VALUE will automatically ignore any space characters that appear in the text.
Pro tip
There are other ways to convert text values to numbers in Excel.
Handling text values and errors
If the text in a cell contains text values (i.e.
1,2,3,A) the formula above will return a #VALUE error.
SUM can then sum the zeros along with other numbers without trouble.
The SUM function then sums the numbers normally.
Note: FILTERXML is only available in Windows Excel.
TEXTSPLIT can split text into rows or columns.
SUM Function
The Excel SUM function returns the sum of values supplied.
These values can be numbers, cell references, ranges, arrays, and constants, in any combination.
SUM can handle up to 255 individual arguments.
FILTERXML Function
The Excel FILTERXML function returns specific data from XML text using the specified XPath expression.
SUBSTITUTE Function
The Excel SUBSTITUTE function replaces text in a given string by matching.
SUBSTITUTE is case-sensitive and does not support wildcards….
