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

Equivalent of InStr and InStrRev in excel

This is a discussion on Equivalent of InStr and InStrRev in excel within the Excel Questions forums, part of the Question Forums category; How to use the following VBA functions InStr InstrRev In excel...

  1. #1
    Spammer
    Join Date
    Jan 2004
    Location
    in the process of locating.
    Posts
    372

    Default Equivalent of InStr and InStrRev in excel

    How to use the following VBA functions

    InStr
    InstrRev


    In excel

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    70,334

    Default Re: Equivalent of InStr and InStrRev in excel

    The Find or Search functions are the equivalent of Instr. For InstrRev you would need to write a UDF.

  3. #3
    New Member
    Join Date
    Dec 2009
    Posts
    1

    Default Re: Equivalent of InStr and InStrRev in excel

    There is a way to do this using Excel native functions. It's a bit awkward, but will work.

    If you want to perform a logical test for the presence or absence of a character in a string, FIND and SEARCH will let you down. Both functions return '#VALUE!' if the search string is not found, which in turn blows the results of your logical test. Instead, you can use the LEN and SUBSTITUTE functions to perform your logical test.

    I'll use an example of a 'Full Name' column I'm trying to break down into 'First Name' and 'Last Name'. The catch is that my 'Full Name' column is sometimes formatted as 'Lastname, Firstname', and sometimes as 'Firstname Lastname', but I want to parse it cleanly regardless of the format of the original column.

    Based on the presence or absence of a comma in the 'Full Name' column, I can reliably break out my desired components most of the time. FIND or SEARCH won't work here, for the reasons above. However, I can use SUBSTITUTE to replace all instances of a comma in my 'Full Name' string with... nothing. Doing so will return a shorter string than the original if any commas are present, and a string the same length as the original if no commas are present. There is no '#VALUE!' result waiting for me here.

    So, for the string 'Simpson, Homer' in cell A4:
    FIND(",", A4) returns a value of 8
    LEN(A4) returns a value of 14
    LEN(SUBSTITUTE(A4, ",", "")) returns a value of 13

    For the string 'Homer Simpson' in cell A4:
    FIND(",", A4) returns '#VALUE!'
    LEN(A4) returns a value of 13
    LEN(SUBSTITUTE(A4, "," "")) returns a value of 13

    So, by comparing the lengths of the strings for your logical test, as below:
    Code:
    IF(LEN(SUBSTITUTE(A4, ",", ""))=LEN(A4), do_when_comma_is_absent, do_when_comma_is_present)
    you get a reliable result without having to bust out the VBA.

  4. #4
    New Member
    Join Date
    Jul 2013
    Posts
    1

    Default Re: Equivalent of InStr and InStrRev in excel

    Not sure how helpfull this is 3+ years later, but here goes: I had addresses put into a string that i needed to parse. Obviously these could vary wildly. Therefor with the following address in cell D2 :

    1234 S Main St. Chicago IL 60609

    I had already parsed the ZIP to column I, and State to Column E was trying to parse the city and I came up with the following:

    =TRIM(MID(D2,FIND("~",SUBSTITUTE(D2," ","~",LEN(D2)-LEN(SUBSTITUTE(D2," ",""))-2)),LEN(D2)-FIND("~",SUBSTITUTE(D2," ","~",LEN(D2)-LEN(SUBSTITUTE(D2," ",""))-2))-LEN(E2&" "&I2)))

    Basically it works like this: It counts the number of spaces (6) subtracts the number of spaces I have already taken care of (2), then I use this in my substitute to replace the 4th space with a character (could have used a special character, but opted for a tilde), and then searched that string for my special character to get my starting location. Then you just have to solve for the number of character you want (total len - found start - used chars) to use in your mid statement. Trim was just superflous, but I couldn't help myself

    FYI: This doesnt work for cities with more that one name .... oh well....
    Last edited by jefkve; Jul 11th, 2013 at 08:23 PM.

  5. #5
    New Member
    Join Date
    Apr 2014
    Posts
    1

    Default Re: Equivalent of InStr and InStrRev in excel

    Just came across this thread in regard to a similar problem I was having. I simply needed to determine if a string was part of a comma separated set of values. I was therefore able to use the following:

    =IF(IFERROR(FIND(H$5,$E29),0)>0,1,0)

    Thankfully the set of values were all unique strings and small enough not to worry about duplicates or false positives.

  6. #6
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    17,195

    Default Re: Equivalent of InStr and InStrRev in excel

    I missed this thread on its first go-around. For those who might be interested, here is an Excel formula that I came up with that is equivalent to VBA's InStrRev function...

    =IFERROR(FIND(B1,A1,LEN(TRIM(LEFT(RIGHT(SUBSTITUTE(B1&A1,B1,REPT(" ",999)),1998),999)))),0)

    Cell A1 contains the text to be searched whereas cell B1 contains the text to look for. The search is case-sensitive... if you want to do a case-insensitive search, then wrap each occurrence of A1 and B1 with an UPPER function call.
    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

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

    Default Re: Equivalent of InStr and InStrRev in excel

    Quote Originally Posted by Rick Rothstein View Post
    I missed this thread on its first go-around. For those who might be interested, here is an Excel formula that I came up with that is equivalent to VBA's InStrRev function...

    =IFERROR(FIND(B1,A1,LEN(TRIM(LEFT(RIGHT(SUBSTITUTE(B1&A1,B1,REPT(" ",999)),1998),999)))),0)

    Cell A1 contains the text to be searched whereas cell B1 contains the text to look for. The search is case-sensitive... if you want to do a case-insensitive search, then wrap each occurrence of A1 and B1 with an UPPER function call.
    Actually, the following array-entered** formula seems to also work as a case-insensitive InStrRev equivalent...

    =MAX(999-IF(MID(A2,999-ROW(1:998),LEN(B2))=B2,ROW(1:998),999))

    **Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.
    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
    Board Regular PeteRooneyHome's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    1,046

    Default Re: Equivalent of InStr and InStrRev in excel

    Rick, - is this the latest response to a thread you've ever had?

    Why is is that if I use your non-array formula with "cat" in A1 and "c", "ca" or "cat" in B2, the formula returns 0, when I'd expect it to return 1?
    Yet "t" correctly returns 3 and "a" correctly returns 2?

    #puzzled

    Pete

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

    Default Re: Equivalent of InStr and InStrRev in excel

    Quote Originally Posted by PeteRooneyHome View Post
    Why is is that if I use your non-array formula with "cat" in A1 and "c", "ca" or "cat" in B2, the formula returns 0, when I'd expect it to return 1?
    Yet "t" correctly returns 3 and "a" correctly returns 2?

    #puzzled
    Actually, the problem appears to be worse than that... the formula seems to be doing an InStr rather than an InStrRev when not looking for the first character. I don't know what went wrong here, but I am sure I had this working correctly before I posted it... maybe I copied wrong formula, I don't know. Anyway, I went back to the drawing board and came up with this normally entered formula which seems to work correctly. Let me know if you find any anomalies...

    =IFERROR(FIND(REPT(CHAR(1),LEN(B1)),SUBSTITUTE(A1,B1,REPT(CHAR(1),LEN(B1)),(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1))),0)

    Actually, it turns out my array-entered** formula also needs a minor modification; one, it needs to protect against the text being searched for from being the empty string and, two, the row numbers in the ROW function need to be made absolute to protect against the formula being copied up or down. Here is the fixed version...

    =IF(B1="",0,MAX(999-IF(MID(A1,999-ROW($1:$998),LEN(B1))=B1,ROW($1:$998),999)))

    **Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.
    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
    Board Regular PeteRooneyHome's Avatar
    Join Date
    Sep 2011
    Location
    Manchester, UK
    Posts
    1,046

    Default Re: Equivalent of InStr and InStrRev in excel

    Quote Originally Posted by Rick Rothstein View Post
    Actually, the problem appears to be worse than that... the formula seems to be doing an InStr rather than an InStrRev when not looking for the first character. I don't know what went wrong here, but I am sure I had this working correctly before I posted it... maybe I copied wrong formula, I don't know. Anyway, I went back to the drawing board and came up with this normally entered formula which seems to work correctly. Let me know if you find any anomalies...

    =IFERROR(FIND(REPT(CHAR(1),LEN(B1)),SUBSTITUTE(A1,B1,REPT(CHAR(1),LEN(B1)),(LEN(A1)-LEN(SUBSTITUTE(A1,B1,"")))/LEN(B1))),0)

    Actually, it turns out my array-entered** formula also needs a minor modification; one, it needs to protect against the text being searched for from being the empty string and, two, the row numbers in the ROW function need to be made absolute to protect against the formula being copied up or down. Here is the fixed version...

    =IF(B1="",0,MAX(999-IF(MID(A1,999-ROW($1:$998),LEN(B1))=B1,ROW($1:$998),999)))

    **Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

    Wow.

    I'm impressed, but as it's nealy 19.00 here, am just about to sit down to a far too large shepherd's pie (the pie, not the shepherd!) so will give this a go at work tomorrow!

    As always, thanks for taking the trouble to answer!

    Pete

Page 1 of 2 12 LastLast

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