getting the sum of numbers in a string

[LFKim2018]

I have data in a cell of 1 to maximum of 10 numbers with + sign in between them, but they can not be added as yet for they are part of a string. How would be the formula or the function for its sum?
example:
Column A Column B
1+9.5 sum formula (10.5)
2+11+100 sum formula (113)
37+50+9+7.75 sum formula (103.75)
4+3+1000+77+1+10 sum formula (1095)
and so on....
up to a combination of ten numbers - always with a plus sign in between the numbers.
the numbers can be any number with or without decimal.
using of helper columns is ok.
many many thanks

 

[Tetra201]

Define the following in the Name Manager:
Name: Sum_String
Refers to: =EVALUATE(INDIRECT("RC[-1]",0))

Now, enter =Sum_String in column B and drag-copy down as needed.

 

[LFKim2018]

Define the following in the Name Manager:
Name: Sum_String
Refers to: =EVALUATE(INDIRECT("RC[-1]",0))

Now, enter =Sum_String in column B and drag-copy down as needed.

Mr. Tetra201
Thank you for your reply.
pardon my ignorance, how do I define in the Name manager ?
where do I do it?
many thanks

 

[LFKim2018]

Mr. Tetra201
I got it!
Thank you very very much

 

[Peter_SSs]

You could also try this direct formula approach.

Excel Workbook
	A	B
1	1+9.5	10.5
2	2+11+100	113
3	37+50+9+7.75	103.75
4	4+3+1000+77+1+10	1095
Evaluate sum

 

[Peter_SSs]

If you wanted to avoid the volatile function INDIRECT and you knew there would be no more than, say, 9 numbers to add you could use:
=SUMPRODUCT(MID(SUBSTITUTE(A1&REPT("+0",9),"+",REPT(" ",100)),{1,2,3,4,5,6,7,8,9}*100-99,100)+0)

If there could be lots of numbers to add and you wanted to avoid the volatile function, post back as a longer, non-volatile, version could be constructed.

 

[LFKim2018]

If you wanted to avoid the volatile function INDIRECT and you knew there would be no more than, say, 9 numbers to add you could use:
=SUMPRODUCT(MID(SUBSTITUTE(A1&REPT("+0",9),"+",REPT(" ",100)),{1,2,3,4,5,6,7,8,9}*100-99,100)+0)

If there could be lots of numbers to add and you wanted to avoid the volatile function, post back as a longer, non-volatile, version could be constructed.

Mr. Peter_SSs
That was NEAT!!
the formula though is very very long....
Thank you very very much for the new insight.

 

[LFKim2018]

If you wanted to avoid the volatile function INDIRECT and you knew there would be no more than, say, 9 numbers to add you could use:
=SUMPRODUCT(MID(SUBSTITUTE(A1&REPT("+0",9),"+",REPT(" ",100)),{1,2,3,4,5,6,7,8,9}*100-99,100)+0)

If there could be lots of numbers to add and you wanted to avoid the volatile function, post back as a longer, non-volatile, version could be constructed.

Mr. Peter_ SSs
I noticed you say 9 numbers, I tested it for 10 and it worked too..
many many thanks

 

[Peter_SSs]

I noticed you say 9 numbers, I tested it for 10 and it worked too..

Can you give an example where that works for 10 numbers? It doesn't work for me. By manual calculation this result should be 55, not the 45 returned by the formula.

Excel Workbook
	A	B
5	1+2+3+4+5+6+7+8+9+10	45
Evaluate sum

 

[LFKim2018]

Can you give an example where that works for 10 numbers? It doesn't work for me. By manual calculation this result should be 55, not the 45 returned by the formula.

Evaluate sum

	A	B
5	1+2+3+4+5+6+7+8+9+10	45

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:175px;"><col style="width:76px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B5 =SUMPRODUCT(MID(SUBSTITUTE(A5&REPT("+0",9),"+",REPT(" ",100)),{1,2,3,4,5,6,7,8,9}*100-99,100)+0) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Mr. Peter_SSs
Not only did I tested it for 10, I go as far as 15! and it gave the correct sum.
I have a snipped image but I don't know how to attach it here.
I also have the sample workbook.
thank you