How to retrieve text within ()?
 

Does anyone have any suggestions on how to retrieve text within ()? for
example, Peter (A123) in cell A1, I would like to retrieve A123 into cell B1.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

How to retrieve text within ()?
 

try
=MID(A1,FIND("(",A1)+1,FIND(")",A1)-FIND("(",A1)-1)


"Eric" wrote:

Does anyone have any suggestions on how to retrieve text within ()? for
example, Peter (A123) in cell A1, I would like to retrieve A123 into cell B1.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

How to retrieve text within ()?
 

One way:

=SUBSTITUTE(MID(A1,FIND("(",A1)+1,100),")","")

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestions on how to retrieve text within ()? for
example, Peter (A123) in cell A1, I would like to retrieve A123 into cell
B1.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

How to retrieve text within ()?
 

Use the formula..if the data is in cell A1
= MID(A1, FIND( "(", A1) + 1, SUM( FIND( {"(",")"}, A1) * {-1,1}) - 1 )

hope it works..


"Eric" wrote:

Does anyone have any suggestions on how to retrieve text within ()? for
example, Peter (A123) in cell A1, I would like to retrieve A123 into cell B1.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

How to retrieve text within ()?
 

how to retrieve text within ()? for eg, Peter (A123) in cell A1,
I would like to retrieve A123 into cell B1

In B1:
=MID(A1,SEARCH("(",A1)+1,SEARCH(")",A1)-SEARCH("(",A1)-1)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100 Files:360 Subscribers:56
xdemechanik
---

How to retrieve text within ()?
 

Here is another way to do it...

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

--
Rick (MVP - Excel)


"Eric" wrote in message
...
Does anyone have any suggestions on how to retrieve text within ()? for
example, Peter (A123) in cell A1, I would like to retrieve A123 into cell
B1.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric

How to retrieve text within ()?
 

On Mon, 22 Sep 2008 21:40:00 -0700, Eric
wrote:

Does anyone have any suggestions on how to retrieve text within ()? for
example, Peter (A123) in cell A1, I would like to retrieve A123 into cell B1.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric


And one more method:

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

(where 99 is longer than your longest string)
--ron

How to retrieve text within ()?
 

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

--
Rick (MVP - Excel)


"Ron Rosenfeld" wrote in message
...
On Mon, 22 Sep 2008 21:40:00 -0700, Eric
wrote:

Does anyone have any suggestions on how to retrieve text within ()? for
example, Peter (A123) in cell A1, I would like to retrieve A123 into cell
B1.
Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric


And one more method:

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

(where 99 is longer than your longest string)
--ron

How to retrieve text within ()?
 

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

--
Rick (MVP - Excel)

And, if he wants to add flexibility to his Excel, he could download and install
Longre's free morefunc.xll add-in from http://xcell05.free.fr/ and use this
formula:

=REGEX.SUBSTITUTE(A1,".*\(([^)]*).*","[1]")

or

=REGEX.MID(A1,"(?<=\()([^)]*)(?=\))")

--ron

How to retrieve text within ()?
 

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

How to retrieve text within ()?
 

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

How to retrieve text within ()?
 

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

How to retrieve text within ()?
 

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

How to retrieve text within ()?
 

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