How to best approach this project? [Data manipulation/analysis]

[bkirk420]

Hi guys. I play poker online and am trying to analyze my hand histories in Excel. It is only downloadable as a text file which is very hard to analyze. I am interested in extracting the pot size, hand winner, board, and hand # from each hand. All the information is in **one column** in Excel, about 300 hands and 10,000 cells total.

What would be the best way to go about analyzing this data and extracting the info I want? Formulas? Text to Columns? Putting it in a Pivot Table?

I am attaching 3 example hands from which I am trying to analyze: (I am Ben)

start of hand #6 Fri Jul 19 2024 00:28:10 GMT+0000 (Coordinated Universal Time)}

Dealer: Timothy.

0: Ben (512)

1: c_rizzy (998)

3: Timothy (529)

4: DaBottch (497)

5: dmeek (464)

--- pre-flop (Pot: 0) ---

DaBottch posts blind (2)

dmeek posts blind (5)

Ben raises 30

c_rizzy folds

Timothy calls 30

DaBottch folds

dmeek folds

--- flop (Pot: 67) ---

board: 6d 4h 3d

DaBottch adds 505 chips.

Ben checks

Timothy checks

--- turn (Pot: 67) ---

board: 6d 4h 3d 4d

Ben bets 22

Timothy calls 22

--- river (Pot: 111) ---

board: 6d 4h 3d 4d Qd

Ben checks

Timothy bets 70

Ben raises 460

Timothy folds

390 returned to Ben

Ben wins pot (251), everyone folded.

dmeek adds 541 chips.

start of hand #7 Fri Jul 19 2024 00:29:58 GMT+0000 (Coordinated Universal Time)}

Dealer: DaBottch.

0: Ben (641)

1: c_rizzy (998)

3: Timothy (407)

4: DaBottch (1000)

5: dmeek (1000)

--- pre-flop (Pot: 0) ---

dmeek posts blind (2)

Ben posts blind (5)

c_rizzy raises 15

Timothy folds

DaBottch folds

dmeek folds

Ben folds

10 returned to c_rizzy

c_rizzy wins pot (12), everyone folded.

start of hand #8 Fri Jul 19 2024 00:30:17 GMT+0000 (Coordinated Universal Time)}

Dealer: dmeek.

0: Ben (636)

1: c_rizzy (1005)

3: Timothy (407)

4: DaBottch (1000)

5: dmeek (998)

--- pre-flop (Pot: 0) ---

Ben posts blind (2)

c_rizzy posts blind (5)

Timothy folds

DaBottch raises 15

dmeek raises 52

Ben folds

c_rizzy calls 47

DaBottch calls 37

--- flop (Pot: 158) ---

board: 7h Td 9d

c_rizzy checks

DaBottch checks

dmeek bets 70

c_rizzy calls 70

DaBottch raises 280

dmeek calls 210

c_rizzy folds

--- turn (Pot: 788) ---

board: 7h Td 9d 5h

DaBottch bets 668

dmeek shows (As)

dmeek shows (Ah)

dmeek folds

668 returned to DaBottch

DaBottch wins pot (788), everyone folded.

 

[bkirk420]

One thing that has made it difficult is each hand has a different length of data, it is not a consistent number of cells

 

[Eric W]

Welcome to the MrExcel forum!

How fancy do you want to get, or how do you want the results presented? You can do it by just filtering, if you have a good formula. For example:

Book4
	A	B
1	Data	Code
2	start of hand #6 Fri Jul 19 2024 00:28:10 GMT+0000 (Coordinated Universal Time)}	TRUE
27	board: 6d 4h 3d 4d Qd	TRUE
33	Ben wins pot (251), everyone folded.	TRUE
36	start of hand #7 Fri Jul 19 2024 00:29:58 GMT+0000 (Coordinated Universal Time)}	TRUE
52	c_rizzy wins pot (12), everyone folded.	TRUE
54	start of hand #8 Fri Jul 19 2024 00:30:17 GMT+0000 (Coordinated Universal Time)}	TRUE
86	DaBottch wins pot (788), everyone folded.	TRUE
87
Sheet2

Cell Formulas
Range	Formula
B2,B27,B33,B36,B52,B54,B86	B2	=OR(LEFT(A2,13)="start of hand",ISNUMBER(SEARCH("wins pot",A2)),AND(LEFT(A2,6)="board:",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))=5))

I just put the B2 formula in, which selects which rows to keep, then filtered by column B. It should just display the data you asked for. Not that on hand #8, it did not show the board, because everyone folded before the final board was displayed. We can show all intermediate boards easily, but other than that, it gets trickier. Decent formatted results will probably require VBA.