Returning Values is phrase appears in call

[tbauma]

I’m not sure if it’s possible but I’m looking to do the following.

I’m looking for a formula that will return a number based on a phrase that is in a large text cell and then another number is none of the phrases exist. The only issue is that the phrases share the same words. Below are the phrases and values I’m looking to return.

Provisional Quantity – 1.2
Provisional Quantity as directed – 1.5
Provisional Quantity if ordered – 2

Below are examples of the text that will be in single cells

[TABLE="width: 0"]
<tbody>[TR]
[TD]Clearing and grubbing (MRS04 May 17)
[/TD]
[/TR]
[TR]
[TD]Clearing and grubbing (Provisional Quantity if ordered) (Removal of existing large trees 0.3m
diameter) (MRS04 May 17)
[/TD]
[/TR]
[TR]
[TD]Stripping of topsoil (Provisional Quantity as directed) (For a depth of 200mm) (MRS04 May 17)
[/TD]
[/TR]
[TR]
[TD]Ground surface treatment under embankment, standard (Provisional Quantity) (MRS04 May 17)
[/TD]
[/TR]
[TR]
[TD]Ground surface treatment, in situ treatment of acid sulphate soils with lime (Provisional Quantity if ordered) (For a depth of 100mm) (MRS04 May 17)
[/TD]
[/TR]
[TR]
[TD]Excavation and disposal of Unsuitable Material with individual excavation <= 10 m3 (Provisional Quantity as directed) (MRS04 May 17)
[/TD]
[/TR]
</tbody>[/TABLE]

Cells that will be searched will be in column C and the formula will be in column G

 

[tbauma]

Apologies title should read "Returning Values if phrase appears in cell"