# Extract data between parentheses

#### tkroper

##### Active Member
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)

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))

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>

=if(right(a7)=")",mid(right(a7,3),2,1)+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,"")

And so will this:

=IF(RIGHT(A7)=")",-RIGHT(A7,3),"")

• Rick Rothstein
And so will this:

=IF(RIGHT(A7)=")",-RIGHT(A7,3),"")
Oooh, good one... use the parentheses against themselves... I like it!

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 If you are using XL2007 or higher:

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

Replies
13
Views
235
Replies
0
Views
119
Replies
7
Views
304
Replies
1
Views
370
Replies
19
Views
469

### Forum statistics

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.

### Which adblocker are you using?    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

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