Find and split numerical value from string

[vpranitha]

Hello,

I have 3 scenarios of strings like below

P(23) D(2) O(3)
Y(24) DOR(3) O(2)
YM(23) DOR1(3) O(3)

Goal is to create a formula to capture number in O for diff string examples!! and split and sum all numbers in a diff column .

Assistance is greatly appreciated.

Thank you

 

[Snakehips]

Macropod's first formula can be modified as below to better extract the O value

Excel 2007
	A	B
1	P(23) D(2) O(3)	3
2	Y(24) DOR(3) O(2)	2
3	YM(23) DOR1(3) O(3)	3
4	MH(56) MO(44) O(35)	35
Sheet2

Cell Formulas
Range	Formula
B1	=-RIGHT(A1,LEN(A1)-SEARCH(" o(",A1)-1)