# How to extract a value from between brackets

#### Oldie

##### New Member
What a great site.
I've looked about here but haven't been able to find the answer although I'm sure it will be somewhere.
I have a value that is bracketed and I just want the number extracted to another cell. I've shown three part lines of the data I need to extract from.
Cd 56.5, (53.5), 1:34.16, (36.22),
53.0, (53.0), 1:36.32, (37.36), 6/1,
-2.0, (54.0), 1:59.26, (36.52), 9/1,
The values I need are 53.5 from line one-53.0 from line two & 54.0 from line three.
As you can see they don't always sit in the same column.
I'm a very basic Excel user so do be kind.
Can someone help me out please?

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Scott Huish

##### MrExcel MVP
Hi, welcome to the board!

It looks like you just want the first number in parentheses.

Perhaps:
=REPLACE(LEFT(A1,FIND(")",A1)-1),1,FIND("(",A1),"")

Copy down.

If you want it to actually be a number rather than a text string add a +0 to the end.
Excel Workbook
AB
1Cd 56.5, (53.5), 1:34.16, (36.22),53.5
253.0, (53.0), 1:36.32, (37.36), 6/1,53.0
3-2.0, (54.0), 1:59.26, (36.52), 9/1,54.0
Sheet

#### Oldie

##### New Member
Sorry Hotpepper, I didn' explain myself well at all.
I'm not sure how to display it but the data sits in five columns.
Actually there are heaps more columns but five should do for the excercise.
The top line would read as follows:
A - B - C - D - E
Cd - 56.5, - (53.5), - 1:34:16, - (36.22),
In this instance 53.5 resides in column "C" but could be "B" or "D".
The column next to the right is a time (1min.34.16secs)
It would be dead easy if the bracketed number I want was always in the same column, but it isn't.

#### Richard Schollar

##### MrExcel MVP
Hi

Are these values text or true numbers (you can format numbers to appear as - (53.5) for example)?

#### Oldie

##### New Member
They are freely available and copied from a website into notepad then imported into Excel.
I'm new to this but I suspect your answer is that it is text.
I have been able to extract the numbers I need from some of the other cells, but this one has me stumped.

#### Richard Schollar

##### MrExcel MVP
This is a bit chunky-clunky and there are probably better solutions, but give the following a try:
Excel Workbook
ABCDEF
1Cd56.5,(53.5),1:34:16,(36.22),53.5
Sheet2

#### barry houdini

##### MrExcel MVP
Do you actually have commas in the cells? Assuming not perhaps this will work

=INDEX(A1:E1,MATCH(TRUE,INDEX(A1:E1+0<0,0),0))*-1

#### Krishnakumar

##### Well-known Member
Hi,

=MID(CONCATENATE(A1,B1,C1,D1,E1),FIND("(",CONCATENATE(A1,B1,C1,D1,E1))+1,SUM(FIND({"(",")"},CONCATENATE(A1,B1,C1,D1,E1))*{-1,1})-1)

HTH

L

#### Legacy 14611

##### Guest
I see you have two bracketed expressions on each line.

Do want just one of them extracted (if so which one), or both?

#### Oldie

##### New Member
Thanks Richard- it worked just fine in the example but I couldn't get it to work in the spreadsheet.
There are numerous columns and where you have cell F1 in the example, in practice it would be out around AK1.
In other words, the value is sitting approx. 20 columns back to the left.
I assume there is a value in your formula that counts left of a cell/s but I couldn't find it.
And Barry - yes, there are commas in the data.
Tried your formula just in case without success.
Thanks guys.

Replies
6
Views
233
Replies
3
Views
226
Replies
1
Views
421
Replies
1
Views
802
Replies
0
Views
153

1,191,177
Messages
5,985,139
Members
439,941
Latest member
robertv13

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