+ Reply to Thread
Results 1 to 10 of 10

Use InStr function in formula?

  1. #1
    Lee Hunter
    Guest

    Use InStr function in formula?

    Is there anyway to use the InStr function in a formula or must I create a VBA
    function to do it?

    Thanks,
    Lee

  2. #2
    Bob Phillips
    Guest

    Re: Use InStr function in formula?

    Use SEARCH or FIND. Help will give details.

    --

    HTH

    Bob Phillips

    (remove nothere from the email address if mailing direct)

    "Lee Hunter" <[email protected]> wrote in message
    news:[email protected]...
    > Is there anyway to use the InStr function in a formula or must I create a

    VBA
    > function to do it?
    >
    > Thanks,
    > Lee




  3. #3
    Dave Peterson
    Guest

    Re: Use InStr function in formula?

    You can use =search() or =find()

    =if(isnumber(search("something",a1)),"found it","nope")

    =find() is case sensitive
    =search() isn't.

    Lee Hunter wrote:
    >
    > Is there anyway to use the InStr function in a formula or must I create a VBA
    > function to do it?
    >
    > Thanks,
    > Lee


    --

    Dave Peterson

  4. #4
    Lee Hunter
    Guest

    Re: Use InStr function in formula?

    Thanks you gentlemen. I'm sorry that I asked the wrong question. I actually
    need to search from the end of the string and need the InStrRev function.
    Possible without code?

    Lee

    "Dave Peterson" wrote:

    > You can use =search() or =find()
    >
    > =if(isnumber(search("something",a1)),"found it","nope")
    >
    > =find() is case sensitive
    > =search() isn't.
    >
    > Lee Hunter wrote:
    > >
    > > Is there anyway to use the InStr function in a formula or must I create a VBA
    > > function to do it?
    > >
    > > Thanks,
    > > Lee

    >
    > --
    >
    > Dave Peterson
    >


  5. #5
    Dave Peterson
    Guest

    Re: Use InStr function in formula?

    One way:

    =LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="e"),
    ROW(INDIRECT("1:"&LEN(A1))))

    Change "e" to the last character you want to find.

    Lee Hunter wrote:
    >
    > Thanks you gentlemen. I'm sorry that I asked the wrong question. I actually
    > need to search from the end of the string and need the InStrRev function.
    > Possible without code?
    >
    > Lee
    >
    > "Dave Peterson" wrote:
    >
    > > You can use =search() or =find()
    > >
    > > =if(isnumber(search("something",a1)),"found it","nope")
    > >
    > > =find() is case sensitive
    > > =search() isn't.
    > >
    > > Lee Hunter wrote:
    > > >
    > > > Is there anyway to use the InStr function in a formula or must I create a VBA
    > > > function to do it?
    > > >
    > > > Thanks,
    > > > Lee

    > >
    > > --
    > >
    > > Dave Peterson
    > >


    --

    Dave Peterson

  6. #6
    Harlan Grove
    Guest

    Re: Use InStr function in formula?

    Dave Peterson wrote...
    >One way:
    >
    >=LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="e"),
    > ROW(INDIRECT("1:"&LEN(A1))))

    ....

    That's specific to the sought text being a single character rather than
    a possibly variable length substring. Alternatives include

    =FIND(CHAR(127),SUBSTITUTE(A1,A2,CHAR(127),
    (LEN(A1)-LEN(SUBSTITUTE(A1,A2,"")))/LEN(A2)))

    and

    =LOOKUP(33000,FIND(A2,A1,ROW(INDEX(1:65536,1,1):INDEX(1:65536,LEN(A1),1))))

    where A1 is the string to be searched and A2 is the substring sought.


  7. #7
    Tushar Mehta
    Guest

    Re: Use InStr function in formula?

    If you are like me you would probably go with a pass-through VBA function.

    function VBAInStrRev({all the arguments to InstrRev)
    VBAInStrRev = InStrRev({all the arguments to InstrRev)
    end function

    We'll soon hear the howls of all those who prize saving 3.141592 CPU cycles
    over the loss of transparency, maintainability, and ease of understanding.
    Since I am not one of them, yes, I would strongly consider use of the pass-
    through function.

    --
    Regards,

    Tushar Mehta
    www.tushar-mehta.com
    Excel, PowerPoint, and VBA add-ins, tutorials
    Custom MS Office productivity solutions

    In article <[email protected]>,
    [email protected] says...
    > Thanks you gentlemen. I'm sorry that I asked the wrong question. I actually
    > need to search from the end of the string and need the InStrRev function.
    > Possible without code?
    >
    > Lee
    >
    > "Dave Peterson" wrote:
    >
    > > You can use =search() or =find()
    > >
    > > =if(isnumber(search("something",a1)),"found it","nope")
    > >
    > > =find() is case sensitive
    > > =search() isn't.
    > >
    > > Lee Hunter wrote:
    > > >
    > > > Is there anyway to use the InStr function in a formula or must I create a VBA
    > > > function to do it?
    > > >
    > > > Thanks,
    > > > Lee

    > >
    > > --
    > >
    > > Dave Peterson
    > >

    >


  8. #8
    Harlan Grove
    Guest

    Re: Use InStr function in formula?

    Tushar Mehta wrote...
    >If you are like me you would probably go with a pass-through VBA function.
    >
    >function VBAInStrRev({all the arguments to InstrRev)
    > VBAInStrRev = InStrRev({all the arguments to InstrRev)
    >end function
    >
    >We'll soon hear the howls of all those who prize saving 3.141592 CPU cycles
    >over the loss of transparency, maintainability, and ease of understanding.
    >Since I am not one of them, yes, I would strongly consider use of the pass-
    >through function.


    It's more than a few CPU cycles. The Excel/VBA udf interface isn't
    quick. Also, unless there's documentation that goes along with that
    udf, there's theoretical transparency and maintainability only for the
    OP.

    But there are other considerations. UDFs are considered macros in the
    context of macro security. So the macros would need to be certified in
    order to run on other PCs if they'd be used in business environments in
    which macro security is usually set to high. And there'll soon be the
    added complication that Excel 12 won't support such UDFs in web
    services. Maybe not a concern for the OP, but legacy VBA udfs will soon
    become a big headache for Excel developers. Nasty, long formulas using
    only built-in functions, on the other hand, would still work even in
    web services. Portability not a concern for you?

    There's also the point of using the best tool for the task. In this
    case, an argument could be made for using Laurent Longre's MOREFUNC.XLL
    add-in. Finding the last/rightmost instance of a substring (ss) in a
    given string (s) could be acomplished using regular expressions.

    =REGEX.FIND(s,ss&"(?!.*"&ss&".*)")

    Why arguably better? Consider finding the last instance of 'the' in

    Now is the time for all good men to come to the aid of their country.

    As a simple substring, it'd be the 1st 3 chars of the word 'their', but
    if what's actually wanted is the *word* 'the'? It's relatively trivial:
    add '\b' to both ends of ss: '\bthe\b'. Finding the last instance of a
    whole word would be no trivial exercise in VBA using InStrRev or not.
    This also adds the advantages that MOREFUNC.XLL's functions *are*
    documented, and as an XLL add-in, it doesn't trigger macro security.

    In an ideal world, Excel's FIND and SEARCH would take negative 3rd
    arguments, which would mean search from right to left from the given
    position (the absolute value of the 3rd argument), but Microsoft
    doesn't seem to want to bother to improve or extend existing text
    functions.


  9. #9
    Registered User
    Join Date
    08-13-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Use InStr function in formula?

    Try

    =IF(AND(B2="",NOT(ISERROR( SEARCH("mes",A2)))),"YES","NO")

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,375

    Re: Use InStr function in formula?

    Very kind of you, but the last post to this thread was over 11 years ago ...
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1