Selecting the 5th and 6th characters from right end of a string

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

=LEFT(A1,LEN(A1)-7) This will give you the stripped down text

Then, use the autofilter to filter out the "\SP" and "\TQ" data. Choose the 'contains' option in the filter.

Originally Posted by Tony Valko

Maybe this...

This formula entered in B2 and copied down:

=IF(OR(LEFT(RIGHT(A2,6),2)={"SP","TQ"}),LEFT(A2,LEN(A2)-7),A2)

That worked a treat - thanks Biff.

Thanks to everyone else too who respoonded, this one fits best with my (albeit limited) understanding of how excel formulas work.

Originally Posted by Tony Valko

You're welcome. Thanks for the feedback!


If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

No worries, and cheers for the steer on management of threads

I've got it working too now - it was a silly typo - nothing to do with string length at all.

Thanks,

Dave