How to retrieve text within ()?

[Eric]

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

 

[JMB]

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

 

[T. Valko]

One way:

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

 

[omnitechsupport]

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

hope it works..

 

[Max]

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
---

 

[Rick Rothstein]

Here is another way to do it...

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein]

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>

 

[Ron Rosenfeld]

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>

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein]

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.

 

[Ron Rosenfeld]

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

 

[T. Valko]

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 here:

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

 

[Rick Rothstein]

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