Nesting with "does not contain" function

hugomiguelnn

New Member
Joined
Mar 14, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,400
Office Version
  1. 365
Platform
  1. Windows
Try this out:

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

Watch MrExcel Video

Forum statistics

Threads
1,130,042
Messages
5,639,731
Members
417,108
Latest member
Thein Than

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