# if cell contains certain text, amongst lots of other text..

#### chuf

##### Well-known Member
morning all,

i have a huge list of data and i want to extract the cost of certain jobs, from column S, if certain text is contained within column F.

ie if it says "kitchen" anywhere within the text in column F then show the costs that relates to that row that is contained in column S.

Are you able to help please. i just can't search the whole text and get it to tell me if that one word is in there.

Many thanks,

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

##### MrExcel MVP
=INDEX(S:S,MATCH("*"&A2&"*",F:F,0))

where A2 houses a substring of interest.

#### chuf

##### Well-known Member
Thanks,

i've just been reading a book on this formula.. and had tried it but it came up with a name error #NAME?

PRIMARY - KITCHEN CANOPY is shown in column G

and because it contains the word kitchen

and i want to show the value in column S in column X

Your formula makes perfect sense and i cannot see why its not working

#### barry houdini

##### MrExcel MVP
Don't see how Aladin's formula would give you #NAME? error unless you have #NAME? errors in your data or if you transcribed the formula incorrectly

Will "kitchen" occur more than once in column G? If so and you want to sum all associated values in column S

=SUMIF(G:G,"*kitchen*",S:S)

or with "kitchen" in A2

=SUMIF(G:G,"*"&A2&"*",S:S)

#### shippey121

##### Well-known Member
try this

Code:
``=IF(ISERROR(FIND(G1,f1,1)),"",IF(FIND(G1,f1,1),s1,""))``

where S1 is the cost
G1 is the word kitchen substitue both G1's for anyother cell

HTH

#### chuf

##### Well-known Member
Thanks all,
I'll try when i get home tonight.
Don't know where i'd be without you lot when i'm at my wits end.

Replies
1
Views
60
Replies
14
Views
286
Replies
6
Views
389
Replies
5
Views
396
Replies
20
Views
314

1,137,204
Messages
5,680,160
Members
419,887
Latest member
Vasokir

### 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.

### Which adblocker are you using?

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

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