Extracting Certain Text From A Cell

[ExcelStudent751]

Hello,

I am currently trying to extract the Data No. within parenthesis from a cell. The only problem is the formula I have written out only works for a cell with only one set of parenthesis instead of multiple. Formula below and example below. G19 is line where below statement is.

=MID(C19,SEARCH("Data No",C19)+0,SEARCH(")",C19)-SEARCH("Data No",C19))

HCL (Hydrochloric acid) (Data No ####-##–#) (provided for in textbook)

Thanks!

 

[Fluff]

Maybe
=TRIM(LEFT(SUBSTITUTE(MID(A2,SEARCH("Data No",A2),100),")",REPT(" ",100)),100))

 

[ExcelStudent751]

Thanks!

 

[Fluff]

You're welcome