Nesting with "does not contain" function

[hugomiguelnn]

Hi all,

I have a report that I am trying to automate as it just takes far too long to complete it manually. As main reference to edit my data, I have different titles from different jobs which the naming is not consistent. What I want to do, is identify which jobs are not in UK language, and which ones are translated.

For my translated jobs, the text column I use as reference contains the abbreviation of the country it is destined for, such as US or RU etc. I have created a formula in which every time there is US or RU or any other country abbreviation, excel will tell me it is a translation. However, I still get some mistakes in titles that include for example "USB", etc, as it finds " US" and therefore tells me it is a country abbreviation.

Just for some perspective, I am nesting:
IF function to get my answer if value is true or if not true
OR function so that I use multiple arguments, to find out if any of those arguments are true
ISNUMBER function to tell me if there are numbers
FIND function to find the word I want, and if it finds it, it will give me its position in numbers

=IF(OR(ISNUMBER(FIND(" US"; range that I want ))(ISNUMBER(FIND(" RU"; range that I want )));"translation";"original")

An example of a title that will make this formula give me the wrong response is this one:
BOX, MEDICAL USB, 87233

As the country codes "US" or "RU" are not in there, it should give me "original" as answer. However, it gives me instead "translation" because of the word USB.

Anyone has any idea how I can fix this?

 

[steve the fish]

Try this out:

=IF(OR(ISNUMBER(SEARCH({" US "," RU "}," "&A1&" "))),"Found","Not Found")

 

[hugomiguelnn]

Try this out:

=IF(OR(ISNUMBER(SEARCH({" US "," RU "}," "&A1&" "))),"Found","Not Found")

This worked !! Thanks a lot! :D