Extract data between parentheses

tkroper

Active Member
Joined
Feb 2, 2007
Messages
255
Hi friends!

I have a long list beginning either with 4 numbers or 4 numbers followed by parentheses and a single number between them. I have this formula to extract the data between parentheses:

Code:
=MID(A7,FIND("(",A7)+1,SUM(FIND({"(",")"},A7)*{-1,1})-1)

What can I do to eliminate a "#VALUE!" result for cells that do not contain parentheses?

Thank you :)
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
This formula appears to work...

=MID(A7,FIND("(",A7&"(")+1,SUM(FIND({"(",")"},A7&"(0)")*{-1,1})-1)
 
Upvote 0
tkroper,

This will replace the error with a blank cell:
=IF(ISERROR(MID(A7,FIND("(",A7)+1,SUM(FIND({"(",")"},A7)*{-1,1})-1)),"",MID(A7,FIND("(",A7)+1,SUM(FIND({"(",")"},A7)*{-1,1})-1))
 
Upvote 0
This formula appears to work...

=MID(A7,FIND("(",A7&"(")+1,SUM(FIND({"(",")"},A7&"(0)")*{-1,1})-1)
By the way, if by numbers you meant digits (so that there is a single digit between parentheses), then you can use this alternative formula...

=IF(FIND("(",A7&"(")< LEN(A7),MID(A7,FIND("(",A7)+1,1),"")
<LEN(A7),MID(A7,FIND("(",A7)+1,1),"")< html>
 
Upvote 0
=if(right(a7)=")",mid(right(a7,3),2,1)+0,"")
Good point... Todd did say the single digit in parentheses number was at the end of his text. Given that, this formula will also work...

=IF(RIGHT(A7)=")",MID(A7,LEN(A7)-1,1)+0,"")
 
Upvote 0
By the way, if by numbers you meant digits (so that there is a single digit between parentheses), then you can use this alternative formula...

=IF(FIND("(",A7&"(")< LEN(A7),MID(A7,FIND("(",A7)+1,1),"")
<LEN(A7),MID(A7,FIND("(",A7)+1,1),"")< html>

Sorry - I'm at work and was pulled away by other concerns.

Wonderful! Yes, this does the trick.

I guess I should have mentioned that I do have text to the right of these numbers, so the other suggestions didn't work for me.

Thank you all...I truly appreciate all the help from each of you: Rick, Hotpepper & hiker95 :)
 
Upvote 0
If you are using XL2007 or higher:

=IFERROR(-RIGHT(LEFT(A7,FIND(")",A7)),3),"")
 
Upvote 0

Forum statistics

Threads
1,207,169
Messages
6,076,910
Members
446,239
Latest member
Home Nest

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top