IF Function using contains

[kenadams378]

Hi,

I need to put an 'if' formula together that is looking for data that contains a certain number of characters.

For example, my current formula which doesn't work is ( i think the "1J" & "2J" elements need to include the right of A:A for the last 2 digits.

=If(A:A="1J","ALM",If(A:A="2J","ALM",If(A:A="A0663J",ALM",if(A:A="J053EJ","ALM","ELM"))))

Any ideas??

[etaf]

it maybe useful to have a sample of the data

you have A:A - are you looking for the text anywhere in the column
and the IF is just in 1 cell?

IF its just the last two digits then a right will help

IF you want to use this in each row to test that row then you would put

=IF( OR( A2="A0663J", Right(A2,2)="1J" , Right(A2,2)="2J", A:A="J053EJ") , "ALM","ELM")
and copy down the rows

otherwise , you may be able to use a countif() with a wild card to search the entire column and then return a result into just 1 cell

BUT perhaps you would

Please upload a sample of your workbook to the forum, Make sure you have removed any private information, remember this is a public forum and so available to anyone
Would like to see an example of your data and also a manual mock up of the expected results you want to achieve.

To attach a file to your post,
click "Go advanced" (next to quick post),
scroll down until you see "manage Attachments",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.