Results 1 to 3 of 3
  1. #1
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480

    Can I use instr for this? IF InStr(FirstString, SecondString) > 1 Then

    I have two variables. StringA and StringB I want to be able to determine how many times StringA is in StringB.

    For example...

    StringA = "Cows"
    StringB = "Doing work til the Cows come home, over the rainbow and in traffic Cows everywhere"

    I thought this would mean, the instr would equal 2 in this example. It comes back zero everytime.



    Code:
        If InStr(StringA, StringB) = 2 Then
          ws1.Cells(RowNum, 4) = "Filling out the datas"
          ws1.Cells(RowNum, 6) = "MORE DATA"
        End If

    If not InStr, then what?

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    If you are not sure what instr (or any other function) does, google it first to understand what it does before trying to use it. Instr returns the position of stringB in stringA in terms of number of characters from the beginning and 0 if not found. In your case if you reversed your strings A and B in the instr function it would have returned something like 18, as it is it will return 0. Either way, it is not suitable for your purpose.

    as far as your solution is concerned, try something like

    (len(stringB)-len(replace(stringB,stringA)))/len(stringA)

  3. #3
    redbull's Avatar
    redbull is offline Competent Performer
    Windows XP Access 2010 32bit
    Join Date
    Mar 2012
    Location
    Missouri
    Posts
    480
    Interesting. So instr can be used to determine if StringA is in StringB because it will be > 0, because that's how I've used it in the past.

    So while it works for that, it doesn't actually give you the count of how many times StringA is in StringB.

    This example, "(len(stringB)-len(replace(stringB,stringA)))/len(stringA)" I'm going to try it now but will it tell me how many times StringA is in StringB?

    That is a nifty trick man! works great, here is a working sample for any future googlers who find it.


    msgbox (len(stringB)-len(replace(stringB,stringA, "")))/len(stringA)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Left instr help
    By tmcrouse in forum Forms
    Replies: 2
    Last Post: 05-12-2015, 08:47 AM
  2. InStr() and quotation marks
    By Puebles in forum Programming
    Replies: 3
    Last Post: 11-22-2013, 07:36 AM
  3. IIF with instr
    By yoili in forum Reports
    Replies: 2
    Last Post: 01-22-2013, 01:32 PM
  4. Using InStr() inside Mid()
    By urbi in forum Programming
    Replies: 12
    Last Post: 06-06-2012, 12:00 PM
  5. INSTR(), quick little syntax ?
    By markjkubicki in forum Forms
    Replies: 2
    Last Post: 11-15-2010, 02:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums