Excel - sum cell values divided by "plus"

Question

I have a cell (A1 which have the text "3+2". On the next cell I need to put a formula which is "=A1+1". How can I tell excel that it has to do the sum on A1, so he can sum another values?

score 2 · Answer 1 · answered Sep 14 '18 at 12:04

2

Try to use EVALUATE function. It is best way to do it without VBA, however needs simple trick:

While being in cell B1 add to name manager range "Result" with formula

=EVALUATE($A1)

Than just place in B1 formula

=Result

For more comprehensive description regarding EVALUATE function please refer to this link. Additionaly please notice that EVALUATE function can have different name in your local language.

Btw. similar topic was discussed here

answered Sep 14 '18 at 12:04

TomJohn

747
6
19

1

If you do not use the $ (ie `=EVALUATE(A1)`) then when you enter `=result`, it will always evaluate the contents of the cell to the left. IF A2 was selected when creating the named formula instead of B1, then it would always evaluate the contents of the cell above. – Forward Ed Sep 14 '18 at 14:41

Gary's Student · Accepted Answer · 2018-09-14T12:09:35.170

1

If A1 contains something like

 number+number

then in B1 enter:

=LEFT(A1,FIND("+",A1)-1)+MID(A1,FIND("+",A1)+1,999)+1

If A1 contains an arbitrary numeric expression as text, you would use a VBA UDF.

EDIT#1:

If you have a list of values separated by the + sign, then you can use this array formula:

=SUM((TRIM(MID(SUBSTITUTE(A1,"+",REPT(" ",255)),1+(ROW(A1:A999)-1)*255,255)) & "0")/10)+1

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar:

edited Sep 14 '18 at 12:09

answered Sep 14 '18 at 11:54

Gary's Student

95,722
10
59
99

Thanks, but in cases where I can have "2+4+3" or "2+7+7+6" I'm screwed – Filipe Pires Sep 14 '18 at 11:55
@FilipePires You could use a VBA UDF. – Gary's Student Sep 14 '18 at 11:57
Not a solution in this case. – Filipe Pires Sep 14 '18 at 11:58
@FilipePires I can give you the UDF() if you want it – Gary's Student Sep 14 '18 at 12:01
1

@FilipePires See my **EDIT#1** – Gary's Student Sep 14 '18 at 12:12
Perfect, edit #1 worked as I needed. Can you explain me what's happening on that formula? Just to understand it. – Filipe Pires Sep 14 '18 at 13:16
1

@FilipePires The formula creates an internal array of values that it then sums. – Gary's Student Sep 14 '18 at 13:22

score 1 · Answer 3 · answered Sep 14 '18 at 12:22

Assuming you have your x+xxx+xxxxxxxxxxxx value in cell A1, consider the following formula below:

=SUM(0+TRIM(MID(SUBSTITUTE("+"&A1,"+",REPT(" ",99)),ROW(INDEX(A:A,1):INDEX(A:A,(1+LEN(A1)-LEN(SUBSTITUTE(A1,"+","")))))*99,99)))

When you return the formula, be sure to press CONTROL+SHIFT+ENTER. You can then just drag it down for your other additions.

Nandl66 · Answer 4 · 2018-09-14T12:59:29.547

You need to create a VB Macro:

Open the Tools>Macro>Visual Basic Editor
Right click to Create a new Module (important!)

Double click on the just created new module and paste this code

Public Function EvaluateString(strTextString As String)
    Application.Volatile
    EvaluateString = Evaluate(strTextString)
End Function

Close the VB editor window
Now you can use =EvaluateString(A1) to get the corresponding value of A1.

Excel - sum cell values divided by "plus"

4 Answers4