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

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

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.

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