Struggling with mid/find

[dommmm]

Hi,
I'm wondering how best to approach this...

The formula I have in I2 is:

=IF(RIGHT(H2,5)="00000",IF(ISERROR(MID(F2,FIND("LOCDIFF",F2)+9,FIND(":",F2,FIND("LOCDIFF",F2))-FIND("LOCDIFF",F2)-12)),H2,MID(F2,FIND("LOCDIFF",F2)+9,FIND(":",F2,FIND("LOCDIFF",F2))-FIND("LOCDIFF",F2)-12)),"")

Basically - if the number in column H ends with 5 zeros, then I want it to find return the number (without the preceding - ) in F after "LOCDIFF" . It works fine until there's 2 "LOCDIFF" (the highlighted ones)
(the ISERROR copes with any that don't have LOCDIFF in F)

Advice much appreciated as it's giving me a headache!

 

[Fluff]

Can you please post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Eric W]

Maybe:

=IF(RIGHT(H2,5)="00000",TRIM(MID(SUBSTITUTE(F2," ",REPT(" ",99)),FIND("-",SUBSTITUTE(F2," ",REPT(" ",99)))+1,99)),"")

It finds the string between the - and the next space.