Complex data extraction via formula (Excel

[Range]

Hi

Looking for a bit of help on this one, its a bit complex!! But hoping someone can help!!

So I have the below in column A. Column B is dedicated to Hero contribution to the pot. Column C is dedicated to Jose contribution to pot.

Is there a way from a formula that I can extract the exact contribution from each player?

[TABLE="width: 590"]
<tbody>[TR]
[TD]Beginning a new hand (316) HERO is the dealer[/TD]
[/TR]
[TR]
[TD]HERO has posted a small blind of 10[/TD]
[/TR]
[TR]
[TD]Jose has posted a big blind of 20[/TD]
[/TR]
[TR]
[TD]HERO folded[/TD]
[/TR]
[TR]
[TD]Jose mucked[/TD]
[/TR]
[TR]
[TD]Jose has won 30 chips[/TD]
[/TR]
[TR]
[TD]The hand has finished
[/TD]
[/TR]
[TR]
[TD]Beginning a new hand (791) JosephB is the dealer[/TD]
[/TR]
[TR]
[TD]Jose has posted a small blind of 10[/TD]
[/TR]
[TR]
[TD]HERO has posted a big blind of 20[/TD]
[/TR]
[TR]
[TD]Jose called for 10[/TD]
[/TR]
[TR]
[TD]HERO raised for 40[/TD]
[/TR]
[TR]
[TD]Jose called for 40[/TD]
[/TR]
[TR]
[TD]Dealing the flop 7c2c5c[/TD]
[/TR]
[TR]
[TD]HERO checked[/TD]
[/TR]
[TR]
[TD]Jose bet 40[/TD]
[/TR]
[TR]
[TD]HERO raised for 240[/TD]
[/TR]
[TR]
[TD]Jose called for 200[/TD]
[/TR]
[TR]
[TD]Dealing the turn Tc[/TD]
[/TR]
[TR]
[TD]HERO has gone all-in for 210[/TD]
[/TR]
[TR]
[TD]Jose folded[/TD]
[/TR]
[TR]
[TD]HERO mucked[/TD]
[/TR]
[TR]
[TD]HERO has won 600 chips[/TD]
[/TR]
</tbody>[/TABLE]

 

[XOR LX]

Hi.

For the non-poker initiates, can you clarify what the results should be, and why?

Regards

 

[Range]

Sure

======================================================

So in hand 316 - Jose actually won 10 additional chips to his own.

======================================================

In hand 791 - Hero won 600, however he contributed 20 + 20 + 240 to Joses 10 + 30 + 40 + 200. The 210 was uncalled and therefore not included in the calculation. I am basically trying to keep a running count of the chipcount for each player in column B&C based on info from Column A. Thanks

I can explain further if needed? I guess I need some form of formula that gaugues which player it is and how much they contributed based on their action, ie "Hero" "bet", "called", "raised" or "Jose", "bet", "called", "raised" ... etc

[TABLE="width: 590"]
<tbody>[TR]
[TD]Beginning a new hand (316) HERO is the dealer[/TD]
[/TR]
[TR]
[TD]HERO has posted a small blind of 10[/TD]
[/TR]
[TR]
[TD]Jose has posted a big blind of 20[/TD]
[/TR]
[TR]
[TD]HERO folded[/TD]
[/TR]
[TR]
[TD]Jose mucked[/TD]
[/TR]
[TR]
[TD]Jose has won 30 chips[/TD]
[/TR]
[TR]
[TD]The hand has finished[/TD]
[/TR]
[TR]
[TD]Beginning a new hand (791) JosephB is the dealer[/TD]
[/TR]
[TR]
[TD]Jose has posted a small blind of 10[/TD]
[/TR]
[TR]
[TD]HERO has posted a big blind of 20[/TD]
[/TR]
[TR]
[TD]Jose called for 10[/TD]
[/TR]
[TR]
[TD]HERO raised for 40[/TD]
[/TR]
[TR]
[TD]Jose called for 40[/TD]
[/TR]
[TR]
[TD]Dealing the flop 7c2c5c[/TD]
[/TR]
[TR]
[TD]HERO checked[/TD]
[/TR]
[TR]
[TD]Jose bet 40[/TD]
[/TR]
[TR]
[TD]HERO raised for 240[/TD]
[/TR]
[TR]
[TD]Jose called for 200[/TD]
[/TR]
[TR]
[TD]Dealing the turn Tc[/TD]
[/TR]
[TR]
[TD]HERO has gone all-in for 210[/TD]
[/TR]
[TR]
[TD]Jose folded[/TD]
[/TR]
[TR]
[TD]HERO mucked[/TD]
[/TR]
[TR]
[TD]HERO has won 600 chips[/TD]
[/TR]
</tbody>[/TABLE]

 

[Range]

So I guess I need to use something like

(SEARCH("*HERO*",A1)) and combine this with an OR for (SEARCH("*BET*",A1))(SEARCH("*RAISED*",A1))(SEARCH("*CALLED*",A1)) but how to extract the numerical value from the string cell?

Any suggestions?