How to extract a value from between brackets

Oldie

New Member
Joined
Jul 31, 2007
Messages
9
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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
 
Upvote 0
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.
Thanks for your input.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
I see you have two bracketed expressions on each line.

Do want just one of them extracted (if so which one), or both?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,273
Members
448,559
Latest member
MrPJ_Harper

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