Forum Discussion
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'...
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.