text pattern recognition/extraction

[lizhuett]

I am trying to get Excel to recognize a text pattern to extract which it has recognized in a similar spreadsheet but will not recognize in the current one, both created from Cognos reports. It doesn't matter how many rows I type, it will not pick up what I am doing. This is what the data looks like and what I am trying to get out of it.

If I can only get it to pull COLUMN F from COLUMN E, that's fine. Even better if I can get COLUMN G and COLUMN H to pull from either E or F. 

I really don't understand how this magical feature works, so maybe if someone could explain that to me, I would be able to figure out this problem. Is it something you can use a formula or function to 'teach' Excel to do?

Thanks,

Liz

[#2]

@lizhuett 

If you have Microsoft 365:

In G2: =INDEX(TEXTSPLIT(E2," "),LEN(E2)-LEN(SUBSTITUTE(E2," ",""))+1)

In H2:

=INDEX(TEXTSPLIT(E2," "),3)

Fill down.

Otherwise:

In G2:

=TRIM(RIGHT(SUBSTITUTE(E2," ",REPT(" ",255)),255))

In H2:

=TRIM(MID(SUBSTITUTE(E2," ",REPT(" ",255)),512,255))

Fill down.