Om asks: I have 123,413,413,1352,244 in a single cell.
There are many numbers with commas in between in each cell.
How to Sum all the numbers using a formula?
I don't want to use Text to Column.
By the way, it is Excel 2010.
I use Brad Yundt's three-line VBA function to split the numbers.
Then Excel formulas to sum them.
Also, an easier formula if you have Microsoft 365.
Table of Contents
(0:00) Sum all comma-delimited numbers in cell using an Excel formula
(0:19) Easy solution with TEXTSPLIT
(0:32) Switch to VBA for Brad Yundt Splitter code
(0:55) Using SPLITTER custom function in Excel
(1:25) Wrap up with Wally & Nancy
There are many numbers with commas in between in each cell.
How to Sum all the numbers using a formula?
I don't want to use Text to Column.
By the way, it is Excel 2010.
I use Brad Yundt's three-line VBA function to split the numbers.
Then Excel formulas to sum them.
Also, an easier formula if you have Microsoft 365.
Table of Contents
(0:00) Sum all comma-delimited numbers in cell using an Excel formula
(0:19) Easy solution with TEXTSPLIT
(0:32) Switch to VBA for Brad Yundt Splitter code
(0:55) Using SPLITTER custom function in Excel
(1:25) Wrap up with Wally & Nancy
Transcript of the video:
We have a bunch comma separated values in a single cell.
We want to sum all of those using an Excel formula, no text to columns, and, thanks Om.
Excel 2010. Oh, no.
Okay. So if this was Microsoft 365, it's super easy. We're going to use the text split of B19, breaking at the comma, add a zero to each one of those to convert it back to a number, and then send it into the sum function.
We're good to go.
However, Om is in Excel 2010, so let's save this as XLSM or XLSB. Switch over to VBA with ALT F11.
Here we'll do insert module, and it's just three lines of code.
This is code from Brad Yundt at Great Excel MVP.
Function Splitter(Text As String) Splitter = Split(Text,","). All right.
This is super easy to use.
So equal splitter, that text, it will generate a series of text numbers going across, so we add zero to that, and then just simply sum like that.
Copy that down. All right.
So a simple little formula there with basically three lines.
VBA to make it work.
Much easier if you have Microsoft 365 and we can use text split and the sum function.
Well, hey, thanks to [ inaudible 00:01:26 ] for sending that question in. Thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
We want to sum all of those using an Excel formula, no text to columns, and, thanks Om.
Excel 2010. Oh, no.
Okay. So if this was Microsoft 365, it's super easy. We're going to use the text split of B19, breaking at the comma, add a zero to each one of those to convert it back to a number, and then send it into the sum function.
We're good to go.
However, Om is in Excel 2010, so let's save this as XLSM or XLSB. Switch over to VBA with ALT F11.
Here we'll do insert module, and it's just three lines of code.
This is code from Brad Yundt at Great Excel MVP.
Function Splitter(Text As String) Splitter = Split(Text,","). All right.
This is super easy to use.
So equal splitter, that text, it will generate a series of text numbers going across, so we add zero to that, and then just simply sum like that.
Copy that down. All right.
So a simple little formula there with basically three lines.
VBA to make it work.
Much easier if you have Microsoft 365 and we can use text split and the sum function.
Well, hey, thanks to [ inaudible 00:01:26 ] for sending that question in. Thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
