Tricky find string problem - reverse SEARCH function

[Xipha]

I have been stuck on this all night and am looking for some suggestions. I essentially need to be able to find a character to the left of a string somewhere in the middle of a large text string so I can return a number value. The data is imported from an external database so I cannot change the format of the source data.

Here are a couple of cells of sample data (Referenced as the [@Spills] column in the code below). My problem is I need to extract the value of crude oil spilled, and subtract the value recovered. (In both the examples below the answer is zero: 0.8 m3 - 0.8 m3 =0, and 0.2 m3 - 0.2 m3 =0). I can get my formula to work if the Crude Oil is the first substance listed but I am having difficulty with the case that it is not the first substance listed.

[TABLE="width: 332"]
<tbody>[TR]
[TD="class: xl66, align: left"]0.8m3 CRUDE OIL (0.8m3 RECOVERED) | 4.0m3 SALT/PRODUCED WATER (4.0m3 RECOVERED)[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 332"]
<tbody>[TR]
[TD="class: xl64, align: left"]0.0m3 WASTE (2.5m3 RECOVERED) | 0.2m3 CRUDE OIL (0.2m3 RECOVERED) | 0.1(1000m3) GAS PRODUCTION (RAW)[/TD]
[/TR]
</tbody>[/TABLE]

This is what I have so far:

=IF(SEARCH("CRUDE OIL",[@Spills])<11,VALUE(LEFT([@Spills], SEARCH("m3",[@Spills])-1)),VALUE(MID([@Spills], [U][I][B]Find the | left of CRUDE OIL[/B][/I][/U] +2, [U][I][B]Find the m3 left of CRUDE OIL[/B][/I][/U] - ([U][I][B]Find the | left of CRUDE OIL[/B][/I][/U] +2) )))-IFERROR(IF(MID([@Spills],SEARCH("CRUDE OIL",[@Spills])+10,1)="(",VALUE(MID([@Spills],SEARCH("CRUDE OIL",[@Spills])+11,SEARCH("m3",[@Spills],SEARCH("CRUDE OIL",[@Spills]))-SEARCH("(",[@Spills],SEARCH("CRUDE OIL",[@Spills]))-1)),0),0)

I've underlined the text place holder for sections of the code I am stuck on above. I need to figure out how to search to the left of the CRUDE OIL substring to make this approach work. I'm also open to suggestions of alternate approaches to obtain the results I am looking for.

 

[Rick Rothstein]

Give this formula a try...

=IFERROR(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(LEFT([@Spills],SEARCH("CRUDE OIL",[@Spills])-2)," ",REPT(" ",LEN([@Spills]))),LEN([@Spills]))),"m3","")-MID([@Spills],FIND("(",[@Spills],SEARCH("CRUDE OIL",[@Spills]))+1,FIND("m3",[@Spills],FIND("(",[@Spills],SEARCH("CRUDE OIL",[@Spills]))+1)-FIND("(",[@Spills],SEARCH("CRUDE OIL",[@Spills]))-1),"")

 

[István Hirsch]

Give this a try too. In the formula, change A1 to [@Spills]:

=IFERROR(-LOOKUP(1,(-MID(A1,FIND("CRUDE OIL (",A1)-{4,5,6,7,8,9,10,11},{1,2,3,4,5,6,7,8})))+LOOKUP(1,(-MID(A1,FIND("CRUDE OIL (",A1)+11,{1,2,3,4,5,6,7,8}))),"")

 

[Xipha]

That worked, I just had to modify to subtract 0 in the the case there was no recovered oil so it didn't grab the recovery amount from the next product. Thanks!

=IFERROR(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(LEFT([@Spills],SEARCH("CRUDE OIL",[@Spills])-2)," ",REPT(" ",LEN([@Spills]))),LEN([@Spills]))),"m3","")-IF(FIND("(",[@Spills],SEARCH("CRUDE OIL",[@Spills]))-SEARCH("CRUDE OIL",[@Spills])=10,MID([@Spills],FIND("(",[@Spills],SEARCH("CRUDE OIL",[@Spills]))+1,FIND("m3",[@Spills],FIND("(",[@Spills],SEARCH("CRUDE OIL",[@Spills]))+1)-FIND("(",[@Spills],SEARCH("CRUDE OIL",[@Spills]))-1),0),"")

 

[Xipha]

I missed if there is only one product and no recovery, so I had to add code to catch that error:

=IFERROR(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(LEFT([@Spills],SEARCH("CRUDE OIL",[@Spills])-2)," ",REPT(" ",LEN([@Spills]))),LEN([@Spills]))),"m3","")-IF(IFERROR(FIND("(",[@Spills],SEARCH("CRUDE OIL",[@Spills])),0)-SEARCH("CRUDE OIL",[@Spills])=10,MID([@Spills],FIND("(",[@Spills],SEARCH("CRUDE OIL",[@Spills]))+1,FIND("m3",[@Spills],FIND("(",[@Spills],SEARCH("CRUDE OIL",[@Spills]))+1)-FIND("(",[@Spills],SEARCH("CRUDE OIL",[@Spills]))-1),0),"")

 

[Rick Rothstein]

I missed if there is only one product and no recovery, so I had to add code to catch that error:

=IFERROR(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(LEFT([@Spills],SEARCH("CRUDE OIL",[@Spills])-2)," ",REPT(" ",LEN([@Spills]))),LEN([@Spills]))),"m3","")-IF(IFERROR(FIND("(",[@Spills],SEARCH("CRUDE OIL",[@Spills])),0)-SEARCH("CRUDE OIL",[@Spills])=10,MID([@Spills],FIND("(",[@Spills],SEARCH("CRUDE OIL",[@Spills]))+1,FIND("m3",[@Spills],FIND("(",[@Spills],SEARCH("CRUDE OIL",[@Spills]))+1)-FIND("(",[@Spills],SEARCH("CRUDE OIL",[@Spills]))-1),0),"")

Did you see István's formula in Message #3... it works as well and is much more compact.

 

[Xipha]

Did you see István's formula in Message #3... it works as well and is much more compact.

I did see it, and it worked for most cases except the ones I had to modify for above, since there were a few cells with no recovered product or weird formats. I was able to modify your formula to handle them, but I am not as sure how his works and wasn't sure how to adjust to handle the cells it didn't work for.

e.g.
[TABLE="width: 332"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: left"]1.0m3 CRUDE OIL (ME[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]2.0m3 CRUDE OIL

[/TD]
[/TR]
[TR]
[TD="align: left"]0.0m3 WASTE (2.5m3 RECOVERED) | 0.2m3 CRUDE OIL | 0.1(1000m3) GAS PRODUCTION (RAW)


[/TD]
[/TR]
</tbody>[/TABLE]
I'm still playing with István's trying to learn how it works so I have more options next time

 

[István Hirsch]

I did see it, and it worked for most cases except the ones I had to modify for above, since there were a few cells with no recovered product or weird formats. I was able to modify your formula to handle them, but I am not as sure how his works and wasn't sure how to adjust to handle the cells it didn't work for.

e.g.
[TABLE="width: 332"]
<tbody>[TR]
[TD="align: left"]1.0m3 CRUDE OIL (ME[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]2.0m3 CRUDE OIL
[/TD]
[/TR]
[TR]
[TD="align: left"]0.0m3 WASTE (2.5m3 RECOVERED) | 0.2m3 CRUDE OIL | 0.1(1000m3) GAS PRODUCTION (RAW)

[/TD]
[/TR]
</tbody>[/TABLE]
I'm still playing with István's trying to learn how it works so I have more options next time

Give this a try (to handle structures in post #7):

=IFERROR(-LOOKUP(1,(-MID(A1,FIND("CRUDE OIL",A1)-{4,5,6,7,8,9,10,11},{1,2,3,4,5,6,7,8})))+IFERROR(LOOKUP(1,(-MID(A1,FIND("CRUDE OIL (",A1)+11,{1,2,3,4,5,6,7,8}))),0),"")