CODEWITHSUNDEEP
excelexcel-formula
Filipe Pires
Filipe Pires

Reputation: 147

Excel - sum cell values divided by "plus"

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? Example

Upvotes: 2

Views: 574

Answers (4)

Nandl66
Nandl66

Reputation: 284

You need to create a VB Macro:

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

  3. 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
  4. Close the VB editor window
  5. Now you can use =EvaluateString(A1) to get the corresponding value of A1.

Upvotes: 1

J.Mapz
J.Mapz

Reputation: 511

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.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

If A1 contains something like

 number+number

then in B1 enter:

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

enter image description here

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:

enter image description here

Upvotes: 1

TomJohn
TomJohn

Reputation: 747

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

Upvotes: 2

Related Questions