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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Nov 22, 2016 at 11:36 AM
Related:
- Contain word, return value
- Number to text in word - Guide
- Microsoft word free download - Download - Word processors
- Word free for u - Guide
- Flipkart return comments - Guide
- Word viewer - Download - Office suites
3 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Nov 21, 2016 at 11:54 AM
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
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.
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
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Nov 22, 2016 at 11:36 AM
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:
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
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
