Equivalent of InStr and InStrRev in excel

TheBuGz

Spammer
Joined
Jan 25, 2004
Messages
367
How to use the following VBA functions

InStr
InstrRev


In excel
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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