Forum Discussion

lizhuett's avatar
lizhuett
Copper Contributor
Aug 09, 2023

text pattern recognition/extraction

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

Charting
excel

1 Reply

Sort By
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 09, 2023

    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.

Resources