Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Lee Hunter
 
Posts: n/a
Default 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   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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:
  1. Let's say you have a string of text in cell A1, and you want to find the position of the word "apple" within that string. You can use the following formula:

    Formula:
    =InStr(A1,"apple"
  2. This will return the position of the first occurrence of "apple" within the string in cell A1. If "apple" is not found within the string, the function will return a value of 0.
  3. You can also use the InStr function in combination with other functions to perform more complex operations. For example, you could use the LEFT function to extract a portion of a string that comes before a certain substring. Here's an example:

    Formula:
    =LEFT(A1,InStr(A1,"apple")-1
  4. This will return the portion of the string in cell A1 that comes before the first occurrence of "apple". The -1 at the end of the formula is used to exclude the position of "apple" itself from the result.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Peterson
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Lee Hunter
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dave Peterson
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Tushar Mehta
 
Posts: n/a
Default 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 ,
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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default 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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Price function difference in Output formula vis a vis Manual Calculation abhi_23 Excel Worksheet Functions 0 January 17th 06 08:57 AM
Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 06:18 PM
function CELL() to return the formula in the referenced cell Streep Excel Worksheet Functions 3 August 20th 05 10:24 PM
Formula checking multiple worksheets sonic-the-mouse Excel Worksheet Functions 2 June 5th 05 07:48 PM
Function Formula is displayed not results iloveexcellllll Excel Worksheet Functions 0 January 11th 05 03:29 AM


All times are GMT +1. The time now is 06:34 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"