Extracting a number before a word or phrase between parentheses

%3CLINGO-SUB%20id%3D%22lingo-sub-2596580%22%20slang%3D%22en-US%22%3EExtracting%20a%20number%20before%20a%20word%20or%20phrase%20between%20parentheses%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2596580%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20giving%20you%202%20examples%20of%20the%20data%20that%20I%20need%20to%20extract%20info%20from.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eexample%201%3A%3C%2FP%3E%3CP%3EFINISH%20SIZE%20AFTER%20EB%3A%2012.909%22%20X%2021.750%22.%20(%3CFONT%20color%3D%22%23000000%22%3E3%3C%2FFONT%3E)%20PART%20NEST%2C%20CREATES%20(3)%20700644-09.%20FINISH%20SIZE%3A%203.803%22%20X%2021.750%22.%3C%2FP%3E%3CP%3Eexample%202%3A%3C%2FP%3E%3CP%3EFULL%20SHEET%20(2)%20PART%20NEST%3A%20CREATES%20(1)%20700644-02%20%26amp%3B%20(1)%20700644-0%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20extract%20the%20first%203%20that%20is%20between%20parentheses%20in%20example%201%20and%20the%202%20that%20is%20between%20parentheses%20in%20example%202.%3C%2FP%3E%3CP%3EAs%20you%20can%20see%20the%20number%20will%20fall%20before%20the%20word%2Fphrase%20%22PART%20NEST%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advanced%20for%20any%20help%20that%20you%20can%20offer.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2596580%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2596835%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20a%20number%20before%20a%20word%20or%20phrase%20between%20parentheses%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2596835%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1116831%22%20target%3D%22_blank%22%3E%40Noah_S%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLET(%0A%20%20str%2C%20J9%2C%0A%20%20right%2C%20LEFT(str%2CFIND(%22)%22%2Cstr)-1)%2C%0A%20%20IFERROR(--RIGHT(right%2CLEN(right)%20-%20FIND(%22(%22%2C%20right))%2C%200)%0A)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2596584%22%20slang%3D%22en-US%22%3ERe%3A%20Extracting%20a%20number%20before%20a%20word%20or%20phrase%20between%20parentheses%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2596584%22%20slang%3D%22en-US%22%3EGOT%20IT.%3CBR%20%2F%3E%3DIFERROR(MID(J9%2CFIND(%22(%22%2CJ9)%2B1%2CFIND(%22)%22%2CJ9)-FIND(%22(%22%2CJ9)-1)%2C0)%3CBR%20%2F%3ELETS%20SEE%20IF%20IT%20FAILS%20IN%20DIFFERENT%20CRITERIA.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

I am giving you 2 examples of the data that I need to extract info from.

 

example 1:

FINISH SIZE AFTER EB: 12.909" X 21.750". (3) PART NEST, CREATES (3) 700644-09. FINISH SIZE: 3.803" X 21.750".

example 2:

FULL SHEET (2) PART NEST: CREATES (1) 700644-02 & (1) 700644-0

 

I need to extract the first 3 that is between parentheses in example 1 and the 2 that is between parentheses in example 2.

As you can see the number will fall before the word/phrase "PART NEST".

 

Thanks in advanced for any help that you can offer.

2 Replies
GOT IT.
=IFERROR(MID(J9,FIND("(",J9)+1,FIND(")",J9)-FIND("(",J9)-1),0)
LETS SEE IF IT FAILS IN DIFFERENT CRITERIA.

@Noah_S 

As variant

=LET(
  str, J9,
  right, LEFT(str,FIND(")",str)-1),
  IFERROR(--RIGHT(right,LEN(right) - FIND("(", right)), 0)
)