Find Partial Text Value

  • Thread starter Thread starter DJ_Frustration
  • Start date Start date
D

DJ_Frustration

I'm stuck on this problem and would appreciate any feedback.

I have two columns in a spreadsheet.
_Description__________Price_
Shirt Large
Shirt Small
Shirt Medium
Hat Large
Hat Small

I need to enter a known value in the _Price_ column by searching for
whether or not the text "Large" or "Small" is present in the
_Description_ field. I have tried vlookup but since I can't sort the
spreadsheet its of no use. Also, I can't assume that there are any
restrictions on input characters or size in the _Description_ field

Here's the formula that I'd like use, but i seem to be at a loss with
how to fill in the CONTAINS part: =IF(A1 CONTAINS "Large", 25, A1
CONTAINS "Small", 15, 20)

Any ideas?????

Thanks,

DJ_Frustration
 
Hi DJ,

There is a shorter method with VBA, but the below should work for you.

Description Price
Hat Large
=IF(ISERROR(SEARCH("Large",A2))=FALSE,25,(IF(ISERROR(SEARCH("Small",A2))=FAL
SE,15,20)))
Hat Small
=IF(ISERROR(SEARCH("Large",A3))=FALSE,25,(IF(ISERROR(SEARCH("Small",A3))=FAL
SE,15,20)))
Shirt Large
=IF(ISERROR(SEARCH("Large",A4))=FALSE,25,(IF(ISERROR(SEARCH("Small",A4))=FAL
SE,15,20)))
Shirt Medium
=IF(ISERROR(SEARCH("Large",A5))=FALSE,25,(IF(ISERROR(SEARCH("Small",A5))=FAL
SE,15,20)))
Hat Small
=IF(ISERROR(SEARCH("Large",A6))=FALSE,25,(IF(ISERROR(SEARCH("Small",A6))=FAL
SE,15,20)))
 
I'm stuck on this problem and would appreciate any feedback.

I have two columns in a spreadsheet.
_Description__________Price_
Shirt Large
Shirt Small
Shirt Medium
Hat Large
Hat Small

I need to enter a known value in the _Price_ column by searching for
whether or not the text "Large" or "Small" is present in the
_Description_ field. I have tried vlookup but since I can't sort the
spreadsheet its of no use. Also, I can't assume that there are any
restrictions on input characters or size in the _Description_ field

Here's the formula that I'd like use, but i seem to be at a loss with
how to fill in the CONTAINS part: =IF(A1 CONTAINS "Large", 25, A1
CONTAINS "Small", 15, 20)

Any ideas?????

Thanks,

DJ_Frustration
Click to expand...


=IF(COUNTIF(A1,"*large*"),25,IF(COUNTIF(A1,"*small*"),15,20))


--ron
 
Back
Top