Excel Extract Text after different letters
Given the layout of your text, I think you need to anchor your search on the colon character. There are three pieces required to then find everything after the distance and this should work with pretty much every version of excel.
Assuming that the first race is in A2, you could put following formulae in C2,D2,E2. The first finds the offset of the ":" in the string, in this case 21 and adds 2 to get past the subsequent space. The second formula finds the offset of the next space in the remaining text (e.g. from 23 to end). The final formula then gets all the text from the last offset to the last character.
FIND(":",A3,1)+2
FIND(" ",A2,C2)
RIGHT(A2,LEN(A2)-D2)
The result for each formula is:
21
23
Nov Stks
To consolidate it into a single formula, put the following somewhere in row 3.
RIGHT(A3,LEN(A3)-FIND(" ",A3,FIND(":",A3,1)+2))
The result for A3 would be:
Nursery
And for all cells,
Nov Stks
Nursery
Hcap
Hcap
Hcap Hrd
Hcap
In B1
, formula copied down :
=MID(A1,MIN(FIND({"f ","m "},A1&"f m "))+2,99)
Try this formula: =RIGHT(A1,LEN(A1)-SEARCH(" ",A1,SEARCH(": ",A1)+2))