If (A1 contains the word "invoice", "invoice&

[dixon1983]

Hi,
I have a worksheet that contains a lot of data. There is a description column that contains a reason for the data being on the sheet. The problem is that the data I have in this column isnt in a set format.
Some columns say invoice problem, some say No Invoice etc. There are other reasons also.

I would like to know if its possible to put a formula in the column next to this one which says if the cell next to it contains the word "invoice" anywhere in the cell then return the word "Invoice". Or if the cell contains "pallet" anywhere in the cell to return the word "pallet" etc etc..

Any help on this would be brilliant.

Thanks in advance

 

[lozzablake]

something like:

=IF(ISERROR(SEARCH("invoice",A1,1)),"","Invoice")

 

[dixon1983]

something like:

=IF(ISERROR(SEARCH("invoice",A1,1)),"","Invoice")

how would I change that to put in more options??

e.g pallet / wood / dog / cat etc.....

Thanks for the help

 

[lozzablake]

You would need to nest the IF statements:

=IF(ISERROR(SEARCH("invoice",A1,1)),IF(ISERROR(SEARCH("pallett",A1,1)),"more IF statements here","pallett"),"Invoice")

If you have a lot of words you need to search for, you may be better off running a macro to search for the various terms

 

[erik.van.geit]

Hi,

you can use a formula to do this

   A                        B       C  D         
 1 data                                checklist 
 2 this invoice is wrong    invoice    invoice   
 3 no pallet                pallet     wood      
 4 found no invoice         invoice    pallet    
 5 invoice                  invoice    no        
 6 INVOICE                  invoice              
 7 which pallet is used     pallet               
 8 this is bad wood         wood                 
 9 no wood found            wood                 
10 no way to find the sheet no                   

test
[Table-It] version 06 by Erik Van Geit

RANGE   FORMULA (1st cell)
B2:B10  {=INDEX(D$2:D$6,MATCH(TRUE,ISNUMBER(SEARCH(D$2:D$6,A2)),FALSE))}

{=formula}:
 select first cell
 enter formula without {}
 confirm with Control-Shift-Enter
 then copy down & across

[Table-It] version 06 by Erik Van Geit

NOTE: first item in list will be retrieved
example: "pallet" is found before "no"

kind regards,
Erik

 

[dixon1983]

Thats great. Thanks for both of your helps, much appreciated