Excel - FIND from right to left instead of left to right

Asked By David

20-Nov-09 05:25 PM

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 does not 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!

20-Nov-09 05:37 PM

David,

Try this

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

Mike

20-Nov-09 05:43 PM

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

20-Nov-09 05:52 PM

One way assuming there cannot 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

21-Nov-09 10:25 PM

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)

21-Nov-09 11:32 PM

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

22-Nov-09 01:39 AM

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)

22-Nov-09 07:26 AM

That's good.

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

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

--ron

23-Nov-09 02:23 PM

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