Hi, despite googling the heck out of this, I've so far failed to find a solution to what, I think, should be a relatively straightforward excel query.

I have variable length strings, as shown in the sample cells below

Topography\TAB_Format\SP9200
Topography\TAB_Format\SP9400
OS_Data\Landline
ITN\TileTab\TQ2286


I want to select any record where 5th and 6th character from the right hand end of the string which = either "SP" or "TQ". Where this is the case I'm then looking to truncate the string to remove the last 7 chacters.

Can someone please advise on the kind of formula I should be using? None of what I have so far found using mid or right string appears to be particularly applicable.

Any advice on this would be much appreciated. Thanks