Contain word, return value [Closed]

Report
-
Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
-
Hello,
I would like some help.

cell a1 contains London - abc
cell a2 contains Tokyo - abcd
cell a3 contains Singapore - abcde

I would like cell b1 to return "UK" if cell a1 contains London or "Japan" if cell a2 contains Tokyo or "Singapore" if cell a3 contains Singapore.

Many thanks

3 replies

Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
455
Hi James,

If those are the only conditions then try this:
=if(A1="London","UK",if(A1="Tokyo","Japan","Singapore"))

Best regards,
Trowa
Thanks Trowa, but that did not work.

in short, I have 18 cities in column a. I want to return one of 11 countries in column b.

plus, a1 cell contains more than just "London". it contains other characters as well. example, London - SEF.
another example: cell a4 contains London - Trade. So if a4 contains London, return "UK" in cell b4.
Posts
2693
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 18, 2021
455
Hi James,

Well the logistical statement would look like:
ISNUMBER(SEARCH("London",A1))
But doing that for 18 cities and 11 countries wouldn't be ideal.

So let's try VBA.

In the code below I show how it is done for one city (London > UK) and for multiple cities (Tokyo / Hiroshima > Japan).

Expand the code below with your city/country list:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Columns("A:A"), Target) Is Nothing Then Exit Sub

If Target.Value Like "*London*" Then Target.Offset(0, 1).Value = "UK"

If Target.Value Like "*Tokyo*" Or _
Target.Value Like "*Hiroshima*" Then Target.Offset(0, 1).Value = "Japan"
End Sub


To use the code, right-click on the sheet's tab and select View Code and paste the code in the big white field.

The code will now run automatically, so update or enter data in column A and column B will show the country.

Let me know how this works out for you.

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!