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

dixon1983

Board Regular
Joined
Apr 23, 2005
Messages
175
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

dixon1983

Board Regular
Joined
Apr 23, 2005
Messages
175
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

Well-known Member
Joined
Dec 15, 2005
Messages
818
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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
Code:
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
 

Forum statistics

Threads
1,136,520
Messages
5,676,340
Members
419,619
Latest member
jalme

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