Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.worksheet.functions




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




Answer: Use InStr function in formula?
Using the InStr Function in Excel
Yes, you can use the InStr function in a formula in Excel. The InStr function is used to find the position of a substring within a string. Here's an example of how to use the InStr function in a formula:
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.worksheet.functions




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" wrote in message ... Is there anyway to use the InStr function in a formula or must I create a VBA function to do it? Thanks, Lee 
#4
Posted to microsoft.public.excel.worksheet.functions




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 
#5
Posted to microsoft.public.excel.worksheet.functions




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 
#6
Posted to microsoft.public.excel.worksheet.functions




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 
#7
Posted to microsoft.public.excel.worksheet.functions




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):IN DEX(1:65536,LEN(A1),1)))) where A1 is the string to be searched and A2 is the substring sought. 
#8
Posted to microsoft.public.excel.worksheet.functions




Use InStr function in formula?
If you are like me you would probably go with a passthrough 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.tusharmehta.com Excel, PowerPoint, and VBA addins, tutorials Custom MS Office productivity solutions In article , 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 
#9
Posted to microsoft.public.excel.worksheet.functions




Use InStr function in formula?
Tushar Mehta wrote...
If you are like me you would probably go with a passthrough 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 builtin 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 addin. 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 addin, 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. 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Price function difference in Output formula vis a vis Manual Calculation  Excel Worksheet Functions  
Formula Problem  interrupted by #VALUE! in other cells!?  Excel Worksheet Functions  
function CELL() to return the formula in the referenced cell  Excel Worksheet Functions  
Formula checking multiple worksheets  Excel Worksheet Functions  
Function Formula is displayed not results  Excel Worksheet Functions 