# Sum numbers in cell seperated by a comma

#### FryGirl

##### Well-known Member
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
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
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
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
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
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
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.

#### FryGirl

##### Well-known Member
Thank you Marcelo, this work great.

Thank you tyija1995, but I'm not wanting to separate the values.

#### Marcelo Branco

##### MrExcel MVP
You are welcome. Thanks for the feedback.

M.