Tricky find string problem - reverse SEARCH function

Xipha

New Member
Joined
Oct 29, 2013
Messages
16
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.

0.8m3 CRUDE OIL (0.8m3 RECOVERED) | 4.0m3 SALT/PRODUCED WATER (4.0m3 RECOVERED)

<tbody>
</tbody>
0.0m3 WASTE (2.5m3 RECOVERED) | 0.2m3 CRUDE OIL (0.2m3 RECOVERED) | 0.1(1000m3) GAS PRODUCTION (RAW)

<tbody>
</tbody>

This is what I have so far:

Code:
=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.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,690
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
May 16, 2013
Messages
1,634
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

New Member
Joined
Oct 29, 2013
Messages
16
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

New Member
Joined
Oct 29, 2013
Messages
16

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,690
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Oct 29, 2013
Messages
16
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.
1.0m3 CRUDE OIL (ME
2.0m3 CRUDE OIL

0.0m3 WASTE (2.5m3 RECOVERED) | 0.2m3 CRUDE OIL | 0.1(1000m3) GAS PRODUCTION (RAW)



<colgroup><col></colgroup><tbody>
</tbody>
I'm still playing with István's trying to learn how it works so I have more options next time :)
 

István Hirsch

Well-known Member
Joined
May 16, 2013
Messages
1,634
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.
1.0m3 CRUDE OIL (ME
2.0m3 CRUDE OIL
0.0m3 WASTE (2.5m3 RECOVERED) | 0.2m3 CRUDE OIL | 0.1(1000m3) GAS PRODUCTION (RAW)


<tbody>
</tbody>
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),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,122,738
Messages
5,597,829
Members
414,180
Latest member
Sir Khaya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top