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)

enter image description here


Try this formula: =RIGHT(A1,LEN(A1)-SEARCH(" ",A1,SEARCH(": ",A1)+2))

enter image description here