Page 1 of 2 12 LastLast
Results 1 to 10 of 11

search a string in an excel cell

This is a discussion on search a string in an excel cell within the Excel Questions forums, part of the Question Forums category; 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 I have a lots ...

  1. #1
    New Member
    Join Date
    Jan 2013
    Posts
    25

    Post search a string in an excel cell

    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

    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

  2. #2
    Board Regular Gerald Higgins's Avatar
    Join Date
    Mar 2007
    Location
    Edinburgh
    Posts
    7,150

    Default Re: search a string in an excel cell

    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 by Gerald Higgins; Jan 11th, 2013 at 09:13 AM. Reason: To add welcome
    The following is my SIGNATURE. It's not part of any question or solution I'm posting. If it IS your solution, you've got a very weird problem !

    Sub Macro()
    ActiveCell = "IY" & Right(Application.Name, 5)
    With ActiveCell.Characters(Start:=2, Length:=1).Font
    .Name = "Webdings"
    .Color = 255
    End With
    End Sub

  3. #3
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    11,790

    Default Re: search a string in an excel cell

    Sanjog ,

    Welcome to the MrExcel forum.


    Sample raw data:


    Sheet1

     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  


    Excel tables to the web >> Excel Jeanie HTML 4




    After the macro:


    Sheet1

     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  


    Excel tables to the web >> Excel Jeanie HTML 4




    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.
    Have a great day,
    hiker95

    Windows 8.1, Excel 2007, on a PC.

  4. #4
    New Member
    Join Date
    Jan 2013
    Posts
    25

    Default Re: search a string in an excel cell

    Many Thanks. I will try this ASAP.

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    16,873

    Default Re: search a string in an excel cell

    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)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  6. #6
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,781

    Default Re: search a string in an excel cell

    Also try:

    =SUMPRODUCT(COUNTIF(A1,"*"&{"US","UK"}&"*"),{1,2})+PRODUCT(COUNTIF(A1,"*"&{"US","UK","FR"}&"*"))
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    16,873

    Default Re: search a string in an excel cell

    Quote Originally Posted by Rick Rothstein View Post
    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*"))
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  8. #8
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,781

    Default Re: search a string in an excel cell

    Hi Rick

    Can't you delete the ">0" in your formulas?
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  9. #9
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    16,873

    Default Re: search a string in an excel cell

    Quote Originally Posted by pgc01 View Post
    Hi Rick

    Can't you delete the ">0" in your formulas?
    What if there is more than one US or more than one UK in the text (each would be counted individually then)?
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? Try one of these MrExcel HTML Maker, Excel jeanie or Borders-Copy-Paste

  10. #10
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,781

    Default Re: search a string in an excel cell

    Quote Originally Posted by Rick Rothstein View Post
    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)?
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com