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

chuf

Well-known Member
Joined
Jun 12, 2006
Messages
619
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. :confused:

Many thanks,
 

Excel Facts

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

chuf

Well-known Member
Joined
Jun 12, 2006
Messages
619
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 :confused:
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Mar 21, 2006
Messages
1,001
try this

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

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

HTH
 

chuf

Well-known Member
Joined
Jun 12, 2006
Messages
619
Thanks all, :biggrin:
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. :confused:
 

Forum statistics

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