How to extract a value from between brackets

[Oldie]

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?

 

[Scott Huish]

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
	A	B
1	Cd 56.5, (53.5), 1:34.16, (36.22),	53.5
2	53.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]

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.

 

[Richard Schollar]

Hi

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

 

[Oldie]

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]

This is a bit chunky-clunky and there are probably better solutions, but give the following a try:

Excel Workbook
	A	B	C	D	E	F
1	Cd	56.5,	(53.5),	1:34:16,	(36.22),	53.5
Sheet2

 

[barry houdini]

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]

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

 

[Legacy 14611]

I see you have two bracketed expressions on each line.

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

 

[Oldie]

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.