Extract numbers, with decimal places, from a cell and sum them without VBS

[trishtomorrow]

I have numbers within a cell that always appear within parentheses.

first item (.3) second item (1.2) third item (.5) next item (10.3)

They have decimal values. Without using code (not allowed) is there a way to extract the numbers and sum them in another cell.
I did find the substitute function illustrated below, and it just extracts the number, not the actual values I need. It's close, and maybe someone knows how to tweak it to get the numbers out of the parentheses as decimal values.

=SUBSTITUTE(TRIM(CONCAT(IFERROR(0+MID(A1,SEQUENCE(LEN(A1)),1)," ")))," ","+")

Thanks in advance.

 

[jtakw]

Hi,

Don't understand what result you're looking for, may be:

Book3.xlsx
	A	B	C	D	E	F	G
1	first item (.3) second item (1.2) third item (.5) next item (10.3)	0.3	1.2	0.5	10.3		12.3
Sheet1085

Cell Formulas
Range	Formula
B1:F1	B1	=IFERROR(MID(SUBSTITUTE(SUBSTITUTE($A1,"(",REPT(" ",100),COLUMNS($B1:B1)),")",REPT(" ",100),COLUMNS($B1:B1)),100,100)+0,"")
G1	G1	=SUM(B1:E1)

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B
1
2	first item (.3) second item (1.2) third item (.5) next item (10.3)	12.3
Current

Cell Formulas
Range	Formula
B2	B2	=ABS(SUM(FILTERXML("<k><m>"&SUBSTITUTE(A2," ","</m><m>")&"</m></k>","//m")))

 

[Rick Rothstein]

It would help if you included your version of Excel in your profile. If you have XL365, then Fluff's formula should work for you. If you have an earlier version of Excel, then you can use this formula...

=SUMPRODUCT(0+(0&TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1,"(",")"),")",REPT(" ",300)),(2*ROW(1:50)-1)*300,300))))

Note: This formula assumes the text in the cell is less than 300 characters in length and that there will be no more than 50 numbers to be added together (change the 50 and all the 300 as needed if your limits could be higher).

 

[Fluff]

If you have XL365, then Fluff's formula should work for you.

It should work in 2013 onwards. Although may need array entry in earlier versions.