I need a formula to parse text that looks something like this to extract the
data contained in parentheses.

FI-SL: Local Posting Periods (GCP1)

I was using the following formula which worked just fine ...

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

....until I ran into text with another left parentheses to the left of the
data I want to extract.

C FI Copy company code (G/L account) (OBY2)

Unfortunately the FIND function doesn't provide an option to go from right
to left otherwise this would be easy. Is there another way to extract OBYA
from the text above via formula that will also work on the earlier example?

Thanks in advance!

David,

Try this

=SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"(","*",LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))))),")","")

Mike

"David" wrote:

I need a formula to parse text that looks something like this to extract the
data contained in parentheses.

FI-SL: Local Posting Periods (GCP1)

I was using the following formula which worked just fine ...

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

...until I ran into text with another left parentheses to the left of the
data I want to extract.

C FI Copy company code (G/L account) (OBY2)

Unfortunately the FIND function doesn't provide an option to go from right
to left otherwise this would be easy. Is there another way to extract OBYA
from the text above via formula that will also work on the earlier example?

Thanks in advance!

FI-SL: Local Posting Periods (GCP1)
C FI Copy company code (G/L account) (OBY2)

Is there ever a space in the portion you want to extract?

C FI Copy company code (G/L account) (OB Y2)

--
Biff
Microsoft Excel MVP


"David" wrote in message
...
I need a formula to parse text that looks something like this to extract
the
data contained in parentheses.

FI-SL: Local Posting Periods (GCP1)

I was using the following formula which worked just fine ...

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

...until I ran into text with another left parentheses to the left of the
data I want to extract.

C FI Copy company code (G/L account) (OBY2)

Unfortunately the FIND function doesn't provide an option to go from right
to left otherwise this would be easy. Is there another way to extract
OBYA
from the text above via formula that will also work on the earlier
example?

Thanks in advance!

One way assuming there can't be any left parens after the string you want to
parse


=SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"(","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))))),")","")

--


Regards,


Peo Sjoblom


"David" wrote in message
...
I need a formula to parse text that looks something like this to extract
the
data contained in parentheses.

FI-SL: Local Posting Periods (GCP1)

I was using the following formula which worked just fine ...

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

...until I ran into text with another left parentheses to the left of the
data I want to extract.

C FI Copy company code (G/L account) (OBY2)

Unfortunately the FIND function doesn't provide an option to go from right
to left otherwise this would be easy. Is there another way to extract
OBYA
from the text above via formula that will also work on the earlier
example?

Thanks in advance!

As long as this item in parentheses is always at the end of the text, you
can use this formula...

=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1,"(",REPT(" ",99)),99)),")","")

--
Rick (MVP - Excel)


"David" wrote in message
...
I need a formula to parse text that looks something like this to extract
the
data contained in parentheses.

FI-SL: Local Posting Periods (GCP1)

I was using the following formula which worked just fine ...

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

...until I ran into text with another left parentheses to the left of the
data I want to extract.

C FI Copy company code (G/L account) (OBY2)

Unfortunately the FIND function doesn't provide an option to go from right
to left otherwise this would be easy. Is there another way to extract
OBYA
from the text above via formula that will also work on the earlier
example?

Thanks in advance!

On Fri, 20 Nov 2009 14:25:01 -0800, David
wrote:

I need a formula to parse text that looks something like this to extract the
data contained in parentheses.

FI-SL: Local Posting Periods (GCP1)

I was using the following formula which worked just fine ...

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

...until I ran into text with another left parentheses to the left of the
data I want to extract.

C FI Copy company code (G/L account) (OBY2)

Unfortunately the FIND function doesn't provide an option to go from right
to left otherwise this would be easy. Is there another way to extract OBYA
from the text above via formula that will also work on the earlier example?

Thanks in advance!

The following formula will extract the rightmost (last) parentheses enclosed
substring. It will extract this even if there is extraneous data after the
last parentheses.

So it will extract OBY2 from either of these:

C FI Copy company code (G/L account) (OBY2)
C FI Copy company code (G/L account) (OBY2) old number

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"(",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))))+1,FIND(")",A1,
FIND(CHAR(1),SUBSTITUTE(A1,"(",CHAR(1),LEN(A1)-
LEN(SUBSTITUTE(A1,"(","")))))-1-FIND(CHAR(1),
SUBSTITUTE(A1,"(",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"(","")))))

However, if you there will never be data after the last parentheses, then
Rick's formula is probably more efficient.
--ron

The following formula will extract the rightmost (last) parentheses
enclosed
substring. It will extract this even if there is extraneous data after
the
last parentheses.

So it will extract OBY2 from either of these:

C FI Copy company code (G/L account) (OBY2)
C FI Copy company code (G/L account) (OBY2) old number

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"(",CHAR(1),
LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))))+1,FIND(")",A1,
FIND(CHAR(1),SUBSTITUTE(A1,"(",CHAR(1),LEN(A1)-
LEN(SUBSTITUTE(A1,"(","")))))-1-FIND(CHAR(1),
SUBSTITUTE(A1,"(",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1,"(","")))))

Here is a little bit shorter formula to do the same thing (it was built out
of my previously posted formula)...

=LEFT(TRIM(RIGHT(SUBSTITUTE(A1,"(",REPT(" ",99)),99)),FIND(")",
TRIM(RIGHT(SUBSTITUTE(A1,"(",REPT(" ",99)),99)))-1)

--
Rick (MVP - Excel)

On Sun, 22 Nov 2009 01:39:03 -0500, "Rick Rothstein"
wrote:

Here is a little bit shorter formula to do the same thing (it was built out
of my previously posted formula)...

=LEFT(TRIM(RIGHT(SUBSTITUTE(A1,"(",REPT(" ",99)),99)),FIND(")",
TRIM(RIGHT(SUBSTITUTE(A1,"(",REPT(" ",99)),99)))-1)

That's good.

You could also download and install Longre's free morefunc.xll add-in, and use:

=REGEX.MID(A2,"(?<=\()[^)]+(?=\))",-1)

--ron

That worked perfectly, thanks! Now I just need to reverse engineer it so I
understand how it works.

"Mike H" wrote:

David,

Try this

=SUBSTITUTE(RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1,"(","*",LEN(A1)-LEN(SUBSTITUTE(A1,"(",""))))),")","")

Mike

"David" wrote:

I need a formula to parse text that looks something like this to extract the
data contained in parentheses.

FI-SL: Local Posting Periods (GCP1)

I was using the following formula which worked just fine ...

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

...until I ran into text with another left parentheses to the left of the
data I want to extract.

C FI Copy company code (G/L account) (OBY2)

Unfortunately the FIND function doesn't provide an option to go from right
to left otherwise this would be easy. Is there another way to extract OBYA
from the text above via formula that will also work on the earlier example?

Thanks in advance!