search a string in an excel cell

sanjog

New Member
Joined
Jan 11, 2013
Messages
26
within a single cell I have following character values separated by ,

VN,VI,VE,JP,TH,TW,HK,HM,HN,BR,PH,UM,NI,AR,BO,NZ,SG,KR,KH,ID,UY,SV,AW,US,PY,CR,MY,MF,CX,NF,PR,MO,CA,CC,GT,PA,EC,MX,AU,PE,AS,CN,CO

<tbody>
</tbody>

I have a lots of such entries that vary along the row for the same column no

I want to write a script to check 3 conditions and return the result accordingly

1. If the string contains "US" return 1
2. If the string contains "UK" return 2
3. If the string contains "US" and "UK" return 3
4. If the string contains "US" and "UK" and "FR" return 4

any formula or a VB script to do this effectively for variable rows and fixed column ?

Sanjog
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
There are probably neater ways, but here's one way.

Returns 0 if none of "US", "UK", or "FR" are found.

=IF(ISERROR(FIND("FR",A1)),IF(ISERROR(FIND("US",A1)+FIND("UK",A1)),IF(ISERROR(FIND("UK",A1)),IF(ISERROR(FIND("US",A1)),0,1),2),3),4)

Note that the FIND function is case sensitive.
If you want to be case insensitive, use the SEARCH function instead.

Note that if any of the required values are found more than once, it does not treat them differently.

ps - welcome to the board !
 
Last edited:
Upvote 0
Sanjog ,

Welcome to the MrExcel forum.


Sample raw data:


Excel Workbook
AB
1VN,VI,VE,JP,TH,TW,HK,HM,HN,BR,PH,UM,NI,AR,BO,NZ,SG,KR,KH,ID,UY,SV,AW,US,PY,CR,MY,MF,CX,NF,PR,MO,CA,CC,GT,PA,EC,MX,AU,PE, AS,CN,CO
2VN,VI,VE,JP,TH,TW,HK,HM,HN,BR,PH,UM,NI,AR,BO,NZ,SG,KR,KH,ID,UY,SV,AW,UK,PY,CR,MY,MF,CX,NF,PR,MO,CA,CC,GT,PA,EC,MX,AU,PE, AS,CN,CO
3VN,VI,VE,JP,TH,TW,HK,HM,HN,BR,PH,UM,NI,AR,BO,NZ,SG,KR,KH,ID,UY,SV,AW,FR,PY,CR,MY,MF,CX,NF,PR,MO,CA,CC,GT,PA,EC,MX,AU,PE, AS,CN,CO
4VN,VI,VE,JP,TH,TW,HK,HM,HN,BR,PH,UM,NI,AR,BO,NZ,SG,KR,KH,ID,UY,SV,AW,US,UK,PY,CR,MY,MF,CX,NF,PR,MO,CA,CC,GT,PA,EC,MX,AU,PE, AS,CN,CO
5VN,VI,VE,JP,TH,TW,HK,HM,HN,BR,PH,UM,NI,AR,BO,NZ,SG,KR,KH,ID,UY,SV,AW,US,FR,PY,CR,MY,MF,CX,NF,PR,MO,CA,CC,GT,PA,EC,MX,AU,PE, AS,CN,CO
6VN,VI,VE,JP,TH,TW,HK,HM,HN,BR,PH,UM,NI,AR,BO,NZ,SG,KR,KH,ID,UY,SV,AW,US,PY,CR,MY,MF,CX,NF,PR,MO,CA,CC,GT,PA,EC,MX,AU,PE, AS,CN,CO
7VN,VI,VE,JP,TH,TW,HK,HM,HN,BR,PH,UM,NI,AR,BO,NZ,SG,KR,KH,ID,UY,SV,AW,US,UK,FR,PY,CR,MY,MF,CX,NF,PR,MO,CA,CC,GT,PA,EC,MX,AU,PE, AS,CN,CO
8
Sheet1





After the macro:


Excel Workbook
AB
1VN,VI,VE,JP,TH,TW,HK,HM,HN,BR,PH,UM,NI,AR,BO,NZ,SG,KR,KH,ID,UY,SV,AW,US,PY,CR,MY,MF,CX,NF,PR,MO,CA,CC,GT,PA,EC,MX,AU,PE, AS,CN,CO1
2VN,VI,VE,JP,TH,TW,HK,HM,HN,BR,PH,UM,NI,AR,BO,NZ,SG,KR,KH,ID,UY,SV,AW,UK,PY,CR,MY,MF,CX,NF,PR,MO,CA,CC,GT,PA,EC,MX,AU,PE, AS,CN,CO2
3VN,VI,VE,JP,TH,TW,HK,HM,HN,BR,PH,UM,NI,AR,BO,NZ,SG,KR,KH,ID,UY,SV,AW,FR,PY,CR,MY,MF,CX,NF,PR,MO,CA,CC,GT,PA,EC,MX,AU,PE, AS,CN,CO
4VN,VI,VE,JP,TH,TW,HK,HM,HN,BR,PH,UM,NI,AR,BO,NZ,SG,KR,KH,ID,UY,SV,AW,US,UK,PY,CR,MY,MF,CX,NF,PR,MO,CA,CC,GT,PA,EC,MX,AU,PE, AS,CN,CO3
5VN,VI,VE,JP,TH,TW,HK,HM,HN,BR,PH,UM,NI,AR,BO,NZ,SG,KR,KH,ID,UY,SV,AW,US,FR,PY,CR,MY,MF,CX,NF,PR,MO,CA,CC,GT,PA,EC,MX,AU,PE, AS,CN,CO1
6VN,VI,VE,JP,TH,TW,HK,HM,HN,BR,PH,UM,NI,AR,BO,NZ,SG,KR,KH,ID,UY,SV,AW,US,PY,CR,MY,MF,CX,NF,PR,MO,CA,CC,GT,PA,EC,MX,AU,PE, AS,CN,CO1
7VN,VI,VE,JP,TH,TW,HK,HM,HN,BR,PH,UM,NI,AR,BO,NZ,SG,KR,KH,ID,UY,SV,AW,US,UK,FR,PY,CR,MY,MF,CX,NF,PR,MO,CA,CC,GT,PA,EC,MX,AU,PE, AS,CN,CO4
8
Sheet1





Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code:
Option Explicit
Sub Check3()
' hiker95, 01/11/2013
' http://www.mrexcel.com/forum/excel-questions/678706-search-string-excel-cell.html
Dim ck, s, c As Range, i As Long, n As Long, us As Long, uk As Long, fr As Long
ck = Array("US", "UK", "FR")
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  n = 0: us = 0: uk = 0: fr = 0
  For i = LBound(ck) To UBound(ck)
    If InStr(c, ck(i)) > 0 Then
      n = n + 1
      If ck(i) = "US" Then us = 1
      If ck(i) = "UK" Then uk = 1
      If ck(i) = "FR" Then fr = 1
    End If
  Next i
  Select Case n
    Case 1
      If us = 1 Then c.Offset(, 1) = 1
      If uk = 1 Then c.Offset(, 1) = 2
    Case 2
      If us = 1 And fr = 1 Then c.Offset(, 1) = 1
      If us = 1 And uk = 1 Then c.Offset(, 1) = 3
    Case 3
      If us = 1 And uk = 1 And fr = 1 Then c.Offset(, 1) = 4
  End Select
Next c
Application.ScreenUpdating = True
End Sub


Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the Check3 macro.
 
Upvote 0
Here is a UDF (user defined function) that you can use...
Code:
Function USUKFR(S As String) As Long
  USUKFR = -(InStr(1, S, "US", vbTextCompare) > 0)
  USUKFR = USUKFR - 2 * (InStr(1, S, "UK", vbTextCompare) > 0)
  If USUKFR = 3 Then USUKFR = USUKFR - (InStr(1, S, "FR", vbTextCompare) > 0)
End Function
HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use USUKFR just like it was a built-in Excel function. For example,

=USUKFR(A1)
 
Upvote 0
Also try:

=SUMPRODUCT(COUNTIF(A1,"*"&{"US","UK"}&"*"),{1,2})+PRODUCT(COUNTIF(A1,"*"&{"US","UK","FR"}&"*"))
 
Upvote 0
Here is a UDF (user defined function) that you can use...
Code:
Function USUKFR(S As String) As Long
  USUKFR = -(InStr(1, S, "US", vbTextCompare) > 0)
  USUKFR = USUKFR - 2 * (InStr(1, S, "UK", vbTextCompare) > 0)
  If USUKFR = 3 Then USUKFR = USUKFR - (InStr(1, S, "FR", vbTextCompare) > 0)
End Function
HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use USUKFR just like it was a built-in Excel function. For example,

=USUKFR(A1)
Actually, if you could have blank cells in the range you apply the above UDF to, then use this version of it as it will return an empty string ("") to the cell instead of returning 0 there...
Code:
Function USUKFR(S As String) As Variant
  If Len(S) Then
    USUKFR = -(InStr(1, S, "US", vbTextCompare) > 0)
    USUKFR = USUKFR - 2 * (InStr(1, S, "UK", vbTextCompare) > 0)
    If USUKFR = 3 Then USUKFR = USUKFR - (InStr(1, S, "FR", vbTextCompare) > 0)
  Else
    USUKFR = ""
  End If
End Function
Also, here is a straight formula that does what you want...

=(COUNTIF(A1,"*US*")>0)+2*(COUNTIF(A1,"*UK*")>0)+((COUNTIF(A1,"*US*")>0)+2*(COUNTIF(A1,"*UK*")>0)=3)*COUNTIF(A1,"*FR*")

and if you could be applying the formula to cells that are blank, then use this version of it...

=IF(A1="","",(COUNTIF(A1,"*US*")>0)+2*(COUNTIF(A1,"*UK*")>0)+((COUNTIF(A1,"*US*")>0)+2*(COUNTIF(A1,"*UK*")>0)=3)*COUNTIF(A1,"*FR*"))
 
Upvote 0
What if there is more than one US or more than one UK in the text (each would be counted individually then)?

Doesn't CountIf(A1,"*UK*") return just 0 (if "UK" is not found in A1) or 1 (if "UK" is found in A1, no matter how many times)?
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,253
Members
448,556
Latest member
peterhess2002

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
Back
Top