Sum numbers in single cell

The goal is to sum numbers that appear inside a single cell as seen in column B. Technically, the numbers in each cell are a single text string, and the numbers are separated by commas, which is referred to as a "delimiter". 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.

Summary

In a nutshell, we use the TEXTSPLIT function to split the numbers up by delimiter, then use VALUE to convert the numbers to true numeric values, and then use the SUM function to get a final sum. In the worksheet shown, the formula in cell B5 is:

=SUM(VALUE(TEXTSPLIT(B5,",")))

Working from the inside out, we start with the TEXTSPLIT function.

TEXTSPLIT function

The TEXTSPLIT function is designed to split a text string by a given delimiter into multiple values. The minimal generic syntax for TEXTSPLIT looks like this:

=TEXTSPLIT(text,delimiter)

The result from TEXTSPLIT is an array of separate values. For example, given the text string "A,B,C" with a comma (",") as a delimiter, TEXTSPLIT returns an array like {"A","B","C"}:

=TEXTSPLIT("A,B,C",",") // returns {"A","B","C"}

Notice the delimiter is enclosed in double quotes (""). Moving back to the example, with the text "1,2,3" in cell B5, TEXTSPLIT splits the text at each comma and returns an array with 3 values:

=TEXTSPLIT(B5,",") // returns {"1","2","3"}

This is close to what we need, but notice the result from TEXTSPLIT is text. If we try to SUM the output from TEXTSPLIT directly, the SUM function will ignore the text values and return zero:

=SUM(TEXTSPLIT(B5,",")) // returns 0

We need to convert the text values to numeric values before a sum is calculated. To do this, we use the VALUE function.

VALUE function

The VALUE function converts text that appears in a recognized format (i.e. a number, date, or time format) into a numeric value.  For example, if we provide the text "100", VALUE returns the number 100:

=VALUE("100") returns 100

We can use VALUE to solve this problem by nesting TEXTSPLIT inside VALUE like this:

=VALUE(TEXTSPLIT(B5,","))
=VALUE({"1","2","3"})
={1,2,3}

VALUE converts each text string to a number and returns the result in an array. As a bonus, VALUE will automatically ignore any space characters that appear in the text.

SUM function

The final step in this problem is to sum the numbers returned by the VALUE function. The final formula in cell D5 is:

=SUM(VALUE(TEXTSPLIT(B5,",")))

TEXTSPLIT splits the text string into separate text values, VALUE converts the text values to numbers, and the result from SUM is 6, the sum of 1+2+3.

Other delimiters

To handle numbers in a cell separated by a different value, you can change the delimiter given to TEXTSPLIT. For example, the same numbers in the worksheet below are separated by the "+" symbol instead of a comma. The formula in cell D5 is:

=SUM(VALUE(TEXTSPLIT(B5,"+")))

When the formula is copied down, the results are the same as the original example above.

Pro tip

There are other ways to convert text values to numbers in Excel. One common method is just to add zero like this:

=SUM(TEXTSPLIT(B5,",")+0)

The result is the same, but we are not using the VALUE function.

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. This happens because the VALUE function will return a #VALUE error if it can't convert a value to a number and, when this happens, the SUM function will also return a #VALUE error. One way to handle this problem is to wrap the IFERROR function around VALUE like this:

=SUM(IFERROR(VALUE(TEXTSPLIT(B5,",")),0))

Essentially, we are using IFERROR to convert any errors returned by VALUE to zero. SUM can then sum the zeros along with other numbers without trouble.

Legacy Excel

Older versions of Excel do not provide the TEXTSPLIT function, so there is no direct way to split text values into an array. However, in the Windows version of Excel, you can use a workaround based on the FILTERXML function:

=SUM(FILTERXML("<x><y>"&SUBSTITUTE(B5,",","</y><y>")&"</y></x>","//y"))

In a nutshell, this formula converts the text in B5 to XML by replacing the commas with markup, then it uses FILTERXML to parse the individual numbers into an array. The SUM function then sums the numbers normally. FILTERXML automatically converts the numbers to numeric values in the process, so the VALUE function is not needed. Detailed explanation here.

Note: FILTERXML is only available in Windows Excel.