Extraction of numbers from string

[tomas]

Hi

in attached file there are strings and in columns d:f I hardcoded what I want to extract from strings. I want to accomplish this with formula. Basically a want numbers before first second and third occurance of "p", numbers can have 2 digits and if numbers our enclosed in parenthesis I dont want them.

Thank you

 

[shrivallabha]

With your posted data following formula will work.

Put in cell D2 and then copy down and across.
=TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE("p"&$A2,"p",REPT(" ",99)),COLUMNS($D$1:D1)*99,99)),")",REPT(" ",99)),99))

 

[tomas]

With your posted data following formula will work.

Put in cell D2 and then copy down and across.
=TRIM(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE("p"&$A2,"p",REPT(" ",99)),COLUMNS($D$1:D1)*99,99)),")",REPT(" ",99)),99))

excellent thanks

 

[bosco_yip]

Another option,

D2, copies across and down :

=-LOOKUP(1,-RIGHT(TRIM(MID(SUBSTITUTE("p"&$A2,"p",REPT(" ",50)),COLUMN(A1)*50,50)),{1,2,3}))

Regards
Bosco