Extract (numbers) from a string

JoeExcelHelp
5StarLounger
Posts: 1177
Joined: 22 Jul 2013, 18:29

Extract (numbers) from a string

Post by JoeExcelHelp »

Hi everyone, hope all is well

I have the following line that extracts values in parenthesis but it appears to be looking for the first set of values in parenthesis
My ideal goal would be to find and extract only number values that exist within parenthesis but I guess another solution would be the last value in parenthesis.

Thank You

Example1 - FirstName1, LastName1 (CARE), (08102)
This is producing CARE and I need 08102

Example2 - FirstName2, LastName2 (022222)
This is producing the correct value 022222

Example3 - FirstName3, LastName3 (GSC,CRO), (78054)
This is producing GSC,CRO and I need 78054

Code: Select all

=If(RC1="""","""",mid(RC1,FIND(""("",RC1)+1,FIND("")"",RC1)-FIND(""("",RC1)-1))

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Extract (numbers) from a string

Post by HansV »

Try

Code: Select all

range.FormulaR1C1 = "=SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(RC1,""("",REPT("" "",255)),255)),"")"","""")"
where range is the range you want to apply the formula to.
Best wishes,
Hans

JoeExcelHelp
5StarLounger
Posts: 1177
Joined: 22 Jul 2013, 18:29

Re: Extract (numbers) from a string

Post by JoeExcelHelp »

Thank You Hans, works perfect