FIND from right to left instead of left to right

  • Thread starter Thread starter David
  • Start date Start date
D

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!
 
David,

Try this

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

Mike
 
FI-SL: Local Posting Periods (GCP1)
C FI Copy company code (G/L account) (OBY2)
Click to expand...

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

C FI Copy company code (G/L account) (OB Y2)
 
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
 
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)),")","")
 
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!
Click to expand...

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,"(","")))))
Click to expand...

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)
 
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)
Click to expand...

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.
 
Back
Top