Sum numbers in cell seperated by a comma

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,022
I have two methods to do this, but not quite working out for me yes. The UDF is great but I don't want to save this workbook with macros. The Evaluate method seems to have to be in the same row.

Is there a formula solution?

2, 3, 6, 8 = 19

Code:
Function Test(Rng As Range) As Variant
   Test =Evaluate(Application.Substitute(Rng, ",", "+"))
End Function
Ribbon > Formulas > Defined Names > Define Name

Name: MyArray

Refers to: =EVALUATE("{"&SUBSTITUTE($B8," ","")&"}")

Click OK

Then enter the following formula in C8...

=SUM(MyArray)
 
Last edited:

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
Is the input data ALWAYS in the same format, i.e. 4 numbers separated by comma space ?

If yes, you can use text functions to identify the numbers between the commas.
 

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,022
Hi Gerald,

The numbers could also look like 215, 283, 102, 186, 72, 8, 16

Seven numbers are the max.
 
Last edited:

Gerald Higgins

Well-known Member
Joined
Mar 26, 2007
Messages
9,115
OK well if you want to go down the route of text functions, then personally I would use helper columns to break out your input data into the individual numbers.

For example

In B1
=LEFT(A1,FIND(",",A1)-1)+0

In C1
=LEN(B1)

In D1
=MID(A1,C1+3,100)

In E1
=LEFT(D1,FIND(",",D1)-1)+0
which is basically a repeat of what's in B1, and so on.

Then insert a formula to sum B1, E1, etc.

OR, for a different approach, how about Excel's Text to Columns function ?
 

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,022
Hi Gerald, text to columns will not work in this situation, but I found this CSE formula

=SUM(IFERROR(--MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet3!F9,","," "),"-"," ")," ",REPT(" ",100)),ROW(INDIRECT("1:20"))*100-99,100),))/12
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
648
Depending on how the spreadsheet looks like you can separate the values as they are delimited by a comma.

You can do this with a formula: E.g. Lets say A1 contains "215, 283, 102, 186, 72, 8, 16"
Then in B1 you can use this and copy across to H1:

B1:
Code:
VALUE(TRIM(MID(SUBSTITUTE($A1,CHAR(44),REPT(CHAR(32),LEN($A1))),(COLUMN()-2)*LEN($A1)+1,LEN($A1))))
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,296
Maybe this...


A
B
1
Text​
Sum​
2
215, 283, 102, 186, 72, 8, 16​
882​
3
2, 3, 6, 8​
19​
4
5, 124​
129​
5
125​
125​

B2 copied down
=SUM(INDEX(--("0"&TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",200)),1+(ROW($1:$7)-1)*200,200))),))

M.
 

Forum statistics

Threads
1,077,822
Messages
5,336,564
Members
399,088
Latest member
Swindlestikz

Some videos you may like

This Week's Hot Topics

Top