contain word, return value Closed

Question

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

TrowaD · Answer

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

james · Answer

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.

TrowaD · Answer

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

Contain word, return value

3 responses

Similar discussions

Subscribe To Our Newsletter!