Contain word, return value

Closed
james - Nov 21, 2016 at 06:04 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 22, 2016 at 11:36 AM
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
Related:

3 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Nov 21, 2016 at 11:54 AM
Hi James,

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

Best regards,
Trowa
0
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.
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Nov 22, 2016 at 11:36 AM
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
0