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

 

[Giordano Bruno]

Assuming they start in cell A1 and all have an (O), then =VALUE(MID(A1,FIND(" O(",A1)+3,LEN(A1)-FIND(" O(",A1)-3)) should do the trick.

 

[Macropod]

If you're just after the number following the 'o(', you could use:
=-RIGHT(A1,LEN(A1)-SEARCH("o(",A1))
To sum them (assuming they're in A1:A3), you could use:
=SUM(-RIGHT(A1:A3,LEN(A1:A3)-SEARCH("o(",A1:A3)))
as an array formula.

 

[DILIPandey]

Hi vPranitha,

Two options :-

1) Use below formula to extract required numbers in a separate column :-

=SUBSTITUTE(MID(A1,SEARCH("O(",A1)+2,15),")","")*1

and they you can sum these extracted numbers easily. (Formula assumes your data is there in column A, row 1 onwards)

2) Use "Find & Replace" Ctrl + H option of Excel:-

Make a copy of the column, if you wish. First find *O( and replace with blank and then find ) and replace with blank and then you can sum the remaining numbers easily.


Regards,
DILIPandey

 

[Giordano Bruno]

=-RIGHT(A1,LEN(A1)-SEARCH("o(",A1))
Neat trick Macropod.

 

[vpranitha]

Thanks all the scenario for O should work. What I meant to ask was if a cell A1 had either of the above scenario then I need value of O in one column and sum of all three values p/y/ym+d/dor/dor1+o in another column.

 

[István Hirsch]

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

Don’t you have parts that end in „O”, for example(?):

MH(56) MO(44) O(35)

where all the formulas above fail.

 

[Ingolf]

Giordano Bruno's formula from #2 do it:

=VALUE(MID(A1,FIND(" O(",A1)+3,LEN(A1)-FIND(" O(",A1)-3))

 

[Snakehips]

vpranitha,

Perhaps....

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

Cell Formulas
Range	Formula
B1	=VALUE(MID(A1,FIND(" O(",A1)+3,LEN(A1)-FIND(" O(",A1)-3))
C1	=SUM(MID(SUBSTITUTE(SUBSTITUTE(A1,"(",REPT(" ",99)),")",REPT(" ",99)),{99,297,495},99)+0)

 

[István Hirsch]

Giordano Bruno's formula from #2 do it:

=VALUE(MID(A1,FIND(" O(",A1)+3,LEN(A1)-FIND(" O(",A1)-3))

Yes that’s correct. I have not noticed his formula wrapped in a sentence.