Sum Multiple Numbers Within a Single Cell in Excel | Add Comma Separated Values in One Cell

In this Microsoft Excel video tutorial I explain how to sum up numbers stored in a single cell. Multiple numbers stored in one cell are normally separated by a delimiter such as a comma. If you are using Excel 365 you can sum these numbers using a combination of the SUM and TEXTSPLIT functions. If you are using an older version of Excel, one way of summing the numbers is to create a custom VBA function.

00:00 Introduction
00:35 Use SUM and TEXTSPLIT (Excel 365 users)
02:14 VBA Function 'CELLSPLIT', (code below)

Function CELLSPLIT(cell As String, delimiter As String)
CELLSPLIT = Split(cell, delimiter)
End Function

[IvanCortinas_ES]

Super video. Thank you for the two explanations. I didn't know Split in VBA. Very useful. Thank you Chester.

[Jojosmith]

Hi Chester, Super great video . This is what I am looking for. You make our lives much easier. Thanks millions. Thumbs up as always

[soumyajitdey]

Dear Chester, that was amazing. I am wondering if a cell consists of multiple values separated by forward slash (/) e.g. INC00001 / INC0002, how do I get the actual count of the values (here in this case, there are 2 values ina single cell) without applying delimeter? Is there any way I can get the actual count as 2 please? Looking forward hearing from you. Thank you!

[Rey.K]

Does anyone know how to replicate this in google sheets?

[tonyguerrier]

Question, if I have in cell A1 the number 15 and want to add 4 to the same cell is there a way to go to that cell (A1) type in the number 4 and have the sum of 19 come up when I hit enter?

[GillianJackson-tgxl]

Is there a simple way to just sum numbers as I add them to a single cell? eg if I have 10 in a cell and I then enter 2, it should then show 12

[michaeldennis]

is this possible? row A has the quantity example (30) row B has numbers separated by commas, example (2.500, 3.500, 2.450, 5.235) Row C has numbers separated by commas, example (2.500, 3.500, 2.450, 3.650) this is the formula that I have that gives me the totals for both rows in row D. =SUM(VALUE(CELLSPLIT(B2, ", ")))+SUM(VALUE(CELLSPLIT(C2, ", "))).
I would like to that the total in row D and multiply that by the quanitity of row A. I cant find a fix for this other than putting a formula in row E, example (=d2*a2). is there a way to add that multiplication formula to the formula that already exists in row D?
if you can shed some light I would be appreciative. Michael

[Jojosmith]

Hi Chester how can i copy it with string in excel version 2016? Ex: Client Name : Smith, Simon . I want to copy only the part Smith, Simon. Thanks a lot

[jaimehenrichs]

Does it work, if your values are separated by Alt Enter in a cell, meaning the numbers are stacked in a cell?