FIND from right to left instead of left to right

David

unread,

Nov 20, 2009, 5:25:01 PM11/20/09

to

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!

Mike H

unread,

Nov 20, 2009, 5:37:02 PM11/20/09

to

David,

Try this

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

Mike

T. Valko

unread,

Nov 20, 2009, 5:43:00 PM11/20/09

to

>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" <Da...@discussions.microsoft.com> wrote in message
news:5329890D-2100-4816...@microsoft.com...

Peo Sjoblom

unread,

Nov 20, 2009, 5:52:56 PM11/20/09

to

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" <Da...@discussions.microsoft.com> wrote in message
news:5329890D-2100-4816...@microsoft.com...

Rick Rothstein

unread,

Nov 21, 2009, 10:25:43 PM11/21/09

to

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" <Da...@discussions.microsoft.com> wrote in message
news:5329890D-2100-4816...@microsoft.com...

Ron Rosenfeld

unread,

Nov 21, 2009, 11:32:00 PM11/21/09

to

On Fri, 20 Nov 2009 14:25:01 -0800, David <Da...@discussions.microsoft.com>
wrote:

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

Rick Rothstein

unread,

Nov 22, 2009, 1:39:03 AM11/22/09

to

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

Ron Rosenfeld

unread,

Nov 22, 2009, 7:26:59 AM11/22/09

to

On Sun, 22 Nov 2009 01:39:03 -0500, "Rick Rothstein"
<rick.new...@NO.SPAMverizon.net> 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

David

unread,

Nov 23, 2009, 2:23:03 PM11/23/09

to

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