Extract multiple values from text

[ian0411]

Say I have a string in cell A1 as this: TestA(2h)TestB(15h)TestC(4H), and I would like to extract the numbers and sum them up. For this example, I want 2 + 15 + 4 = 21. Is it possible to do this with only Excel formula and no VBA?

I currently have this array formula from another post but it won't quite work for this scenario (it gets 12 = 2 + 1 + 5 + 4).

{=SUM(VALUE(MID(0&A1,LARGE(ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1))))+1,1)))}

Also, those numbers will always be surrounded by (xxh), xx means values.

Thanks,

Ian

 

[FormR]

Hi, here is one option you can try:

Excel 2013/2016
	A	B
1	TestA(2h)TestB(15h)TestC(4H)	21
Sheet1

Cell Formulas
Range	Formula
B1	=SUMPRODUCT(0+("0"&TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(A1),"h",""),")","("),"(",REPT(" ",999)),ROW(INDIRECT("1:20"))*2*999-998,999))))

 

[ian0411]

@FormR, OMG, you are a genius. Really appreciated your quick response and help.

 

[FormR]

No, I'm just standing on the shoulders of giants - glad to help though

 

[ian0411]

@FormR, can I ask you for help again with another string?

TestA (2h) TestB (15h) TestC (draft) (13H)

Now I have another string with no number but still in the parentheses. Don't know how to do it correctly.

 

[FormR]

@Now I have another string with no number but still in the parentheses.

Excel 2013/2016
	A	B
1	TestA(2h)TestB(15h)TestC(4H)	21
2	TestA (2h) TestB (15h) TestC (draft) (13H)	30
Sheet1

Cell Formulas
Range	Formula
B1	{=SUM(IFERROR(0+(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(A1),"h",""),")","("),"(",REPT(" ",999)),ROW(INDIRECT("1:20"))*2*999-998,999))),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

" >@

Hi, here is one way, pay attention to the Array Formulas notes at the bottom of the example:

Excel 2013/2016
	A	B
1	TestA(2h)TestB(15h)TestC(4H)	21
2	TestA (2h) TestB (15h) TestC (draft) (13H)	30
Sheet1

Cell Formulas
Range	Formula
B1	{=SUM(IFERROR(0+(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(LOWER(A1),"h",""),")","("),"(",REPT(" ",999)),ROW(INDIRECT("1:20"))*2*999-998,999))),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

[ian0411]

@FormR, you know what. I also got the same formula as you did. Guess I am getting better. Haha. But I really appreciated your quick response and your precious time for getting back to me. Have a great day.