adding delimited string

[ratbert14]

I have a "+" delimited string in a single cell that needs to be added together (i.e. "2+.25+1+0.4" should be 3.65) however I'm having problems because of the numbers contain decimals. I was using this formula but it only works with whole integers.

=IF(ISNUMBER(SEARCH("+",M2)), SUMPRODUCT((MID(SUBSTITUTE(M2,"+",""),ROW(INDIRECT("1:"&LEN(SUBSTITUTE(M2,"+","")))),1)+0)), VALUE(M2))

 

[DonkeyOte]

Couple of possibilities (you could also use a UDF - Evaluate) ... point being the Evaluate route will handle variation a little easier than the SUMPRODUCT route.

Excel Workbook
	A	B	C
1	2+.25+1+0.4	3.65	3.65
2	1	1	1
3	1+23.3+-3.45	20.85	20.85
4	1+23.3-3.45	#VALUE!	20.85
5	1*2	#VALUE!	2
Sheet6

#VALUE!

</td></tr></table></td></tr></table>

 

[ratbert14]

Wow thanks - I am obviously a newbie.