On Tue, 23 Sep 2008 10:07:31 -0400, "Rick Rothstein"
wrote:

And we can even take part of your formula and combine it with part of my
formula to produce yet one more method...

=MID(REPLACE(A1,FIND(")",A1),99,""),FIND("(",A1)+ 1,99)

<g


I do like that variation.
--ron

If there is any equivalency in the efficiency of VB functions in relation to
their Excel counterparts, then I sort of favor my original formula
submission with JMB's submission as my second choice. In the VB world, Mid,
Left and Right tend to be among the fastest executing String functions with
InStr (FIND and SEARCH being its Excel equivalents) almost equally quick...
the Replace functionality (SUBSTITUTE being its exact equivalent) tends to
be somewhat slower.... Excel's REPLACE function sort of has a combination of
InStr, Left plus Right all coupled with concatenation as its VB equivalent,
the overall combination of all those functionalities contributing to what I
presume would be a slower execution speed when compared to the faster,
single function String functions of Mid, Left and Right.

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Tue, 23 Sep 2008 10:07:31 -0400, "Rick Rothstein"
wrote:

And we can even take part of your formula and combine it with part of my
formula to produce yet one more method...

=MID(REPLACE(A1,FIND(")",A1),99,""),FIND("(",A1) +1,99)

<g


I do like that variation.
--ron

On Tue, 23 Sep 2008 14:44:56 -0400, "Rick Rothstein"
wrote:

If there is any equivalency in the efficiency of VB functions in relation to
their Excel counterparts, then I sort of favor my original formula
submission with JMB's submission as my second choice. In the VB world, Mid,
Left and Right tend to be among the fastest executing String functions with
InStr (FIND and SEARCH being its Excel equivalents) almost equally quick...
the Replace functionality (SUBSTITUTE being its exact equivalent) tends to
be somewhat slower.... Excel's REPLACE function sort of has a combination of
InStr, Left plus Right all coupled with concatenation as its VB equivalent,
the overall combination of all those functionalities contributing to what I
presume would be a slower execution speed when compared to the faster,
single function String functions of Mid, Left and Right.

I have no idea about the relative speed of Excel vs VBA functions. Certainly
there are many areas in which the two programs are not equivalent.

But I think speed of execution is only one of several goals. And its
importance depends on the application.

Heck, in a previous life, I was programming in machine language.
--ron

All of these formulas have virtually identical calc times:

=SUBSTITUTE(MID(A1,FIND("(",A1)+1,100),")","")
=MID(LEFT(A1,FIND(")",A1)-1),FIND("(",A1)+1,99)
=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)
=MID(REPLACE(A1,FIND(")",A1),99,""),FIND("(",A1)+1 ,99)
=REPLACE(REPLACE(A1,FIND(")",A1),99,""),1,FIND("(" ,A1),"")

The REGEX versions are slower (to be expected).

REGEX.SUBSTITUTE is the "slowest". Twice as "slow" as any of the above.

Calculation timer code he

http://msdn2.microsoft.com/en-us/library/aa730921.aspx


--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
If there is any equivalency in the efficiency of VB functions in relation
to their Excel counterparts, then I sort of favor my original formula
submission with JMB's submission as my second choice. In the VB world,
Mid, Left and Right tend to be among the fastest executing String
functions with InStr (FIND and SEARCH being its Excel equivalents) almost
equally quick... the Replace functionality (SUBSTITUTE being its exact
equivalent) tends to be somewhat slower.... Excel's REPLACE function sort
of has a combination of InStr, Left plus Right all coupled with
concatenation as its VB equivalent, the overall combination of all those
functionalities contributing to what I presume would be a slower execution
speed when compared to the faster, single function String functions of
Mid, Left and Right.

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Tue, 23 Sep 2008 10:07:31 -0400, "Rick Rothstein"
wrote:

And we can even take part of your formula and combine it with part of my
formula to produce yet one more method...

=MID(REPLACE(A1,FIND(")",A1),99,""),FIND("(",A1 )+1,99)

<g


I do like that variation.
--ron

Interesting and good to know. Thanks for running the tests.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
All of these formulas have virtually identical calc times:

=SUBSTITUTE(MID(A1,FIND("(",A1)+1,100),")","")
=MID(LEFT(A1,FIND(")",A1)-1),FIND("(",A1)+1,99)
=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)
=MID(REPLACE(A1,FIND(")",A1),99,""),FIND("(",A1)+1 ,99)
=REPLACE(REPLACE(A1,FIND(")",A1),99,""),1,FIND("(" ,A1),"")

The REGEX versions are slower (to be expected).

REGEX.SUBSTITUTE is the "slowest". Twice as "slow" as any of the above.

Calculation timer code he

http://msdn2.microsoft.com/en-us/library/aa730921.aspx


--
Biff
Microsoft Excel MVP


"Rick Rothstein" wrote in message
...
If there is any equivalency in the efficiency of VB functions in relation
to their Excel counterparts, then I sort of favor my original formula
submission with JMB's submission as my second choice. In the VB world,
Mid, Left and Right tend to be among the fastest executing String
functions with InStr (FIND and SEARCH being its Excel equivalents) almost
equally quick... the Replace functionality (SUBSTITUTE being its exact
equivalent) tends to be somewhat slower.... Excel's REPLACE function sort
of has a combination of InStr, Left plus Right all coupled with
concatenation as its VB equivalent, the overall combination of all those
functionalities contributing to what I presume would be a slower
execution speed when compared to the faster, single function String
functions of Mid, Left and Right.

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Tue, 23 Sep 2008 10:07:31 -0400, "Rick Rothstein"
wrote:

And we can even take part of your formula and combine it with part of my
formula to produce yet one more method...

=MID(REPLACE(A1,FIND(")",A1),99,""),FIND("(",A 1)+1,99)

<g


I do like that variation.
--ron