Extract certain numbers from a column of text cells

Dear community, thank you for helping me. I tried "Text to columns". It works, but I'm trying to automate this so I can finish faster.

I'm trying to extract the "slide number" and "section number" out of the following cells in a column, and count the number of sections on each slide
From this column of cells:

90 ChAT slide 3 section 3 40x1
90 ChAT slide 3 section 4 40x1
90 ChAT slide 3 section 4 40x2
90 ChAT slide 4 section 3 40x1
90 ChAT slide 4 section 4 40x2

Get this output:

slide # Section #
3 3
3 4
3 4
4 3
4 4

The ideal output I want is counts:

section # slide 3 slide 4
3 1 1
4 2 1

I know this is asking a lot, but any part you can help would be really appreciated. If I can only get the slide and section # in 2 columns, that would be great.

Welcome to the forum, yangliu2.

If all you want is the final output then:

Your sample data in A2:A6
In G1: 3 (slide #)
In H1: 4 (slide #)
In F2: 3 (section #)
In F3: 4 (section #)
In G2:

Formula:

Please Login or Register  to view this content.

dragged over and down