The Find or Search functions are the equivalent of Instr. For InstrRev you would need to write a UDF.
How to use the following VBA functions
InStr
InstrRev
In excel
The Find or Search functions are the equivalent of Instr. For InstrRev you would need to write a UDF.
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:
you get a reliable result without having to bust out the VBA.Code:IF(LEN(SUBSTITUTE(A4, ",", ""))=LEN(A4), do_when_comma_is_absent, do_when_comma_is_present)
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 07:23 PM.
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.
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? See here.
Rick's "mini" blog... http://www.excelfox.com/forum/f22/
.
Want to post a small screen shot? See here.
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
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? See here.
Like this thread? Share it with others