FIND from right to left instead of left to right

[David]

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]

David,

Try this

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

Mike

 

[T. Valko]

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)

 

[Peo Sjoblom]

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

 

[Rick Rothstein]

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

 

[Ron Rosenfeld]

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

 

[Rick Rothstein]

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]

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]

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