Extracting Numbers from Text String

[2BLUE4U]

I'm trying to extract grade scores from text strings like this: B (85.2%)

I need a formula that will automatically populate the 2nd column like this...

B (85.2%)

85.2

I've tried so many different lookup formulas (including the one below) and none of them will extract the numbers correctly. Please help!

=LOOKUP(9.9E+307,--LEFT(MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0}, $A2&"1023456789")),999),ROW(INDIRECT("1:999"))))

 

[kevin9999]

Book1
	A	B
1	Grade Summary	Grade Raw Score
2	A (100.0%)	100.0
3	B (85.2%)	85.2
4	B (80.8%)	80.8
5	C (71.7%)	71.7
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=MID(A2, SEARCH("(", A2)+1, SEARCH("%", A2) - SEARCH("(", A2) -1)

 

[dreid1011]

How about this:

Book1
	A	B
1	Grade Summary	Grade Score Raw
2	A (100.0%)	100
3	B (85.2%)	85.2
4	B (80.8%)	80.8
5	D (71.7%)	71.7
6	F (38.1%)	38.1
7	C (78.6%)	78.6
8	C (78.0%)	78
Sheet3

Cell Formulas
Range	Formula
B2:B8	B2	=MID($A2,FIND("(",$A2)+1,4)*1

Of course, this doesn't work for values under 10%. It can be adjusted or just use Kevin's suggestion.

 

[alansidman]

Here is an alternative solution with Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Inserted Text Between Delimiters" = Table.AddColumn(Source, "Text Between Delimiters", each Text.BetweenDelimiters([Grade Summary], "(", ")"), type text),
    #"Replaced Value" = Table.ReplaceValue(#"Inserted Text Between Delimiters","%","",Replacer.ReplaceText,{"Text Between Delimiters"})
in
    #"Replaced Value"

 

[2BLUE4U]

How about this:

Book1
	A	B
1	Grade Summary	Grade Score Raw
2	A (100.0%)	100
3	B (85.2%)	85.2
4	B (80.8%)	80.8
5	D (71.7%)	71.7
6	F (38.1%)	38.1
7	C (78.6%)	78.6
8	C (78.0%)	78
Sheet3

Cell Formulas
Range	Formula
B2:B8	B2	=MID($A2,FIND("(",$A2)+1,4)*1

Of course, this doesn't work for values under 10%. It can be adjusted or just use Kevin's suggestion.

This formula worked for me...
= MID (E8 , SEARCH ( "(", E8 ) + 1 , SEARCH ( ")" , E8 ) - SEARCH ( "(" , E8 ) - 1 )

But now I'm trying to only calculate if E8 is not blank...which I can't seem to do.

 

[kevin9999]

=IF(A2<>"",MID(A2, SEARCH("(", A2)+1, SEARCH("%", A2) - SEARCH("(", A2) -1),"")

 

[dreid1011]

This formula worked for me...
= MID (E8 , SEARCH ( "(", E8 ) + 1 , SEARCH ( ")" , E8 ) - SEARCH ( "(" , E8 ) - 1 )

But now I'm trying to only calculate if E8 is not blank...which I can't seem to do.

Like this:

Book1
	E	F
1
2
3
4
5
6
7	Grade Summary	Grade Score Raw
8	A (100.0%)	100
9	B (85.2%)	85.2
10	B (80.8%)	80.8
11	D (71.7%)	71.7
12	F (38.1%)	38.1
13	C (78.6%)	78.6
14	C (78.0%)	78
15	F (9%)	9
16
17	A (95.5%)	95.5
Sheet3

Cell Formulas
Range	Formula
F8:F17	F8	=IF(E8<>"",MID($E8,FIND("(",$E8)+1,FIND("%",E8)-FIND("(",E8)-1)*1,"")

 

[Peter_SSs]

I need a formula that will automatically populate the 2nd column like this...

B (85.2%)

85.2

This formula worked for me...
= MID (E8 , SEARCH ( "(", E8 ) + 1 , SEARCH ( ")" , E8 ) - SEARCH ( "(" , E8 ) - 1 )

That formula doesn't quite return the result you listed originally. It would return 85.2% for that sample data not just 85.2 as shown in post 1.

Also, just confirming that you are happy to return a text value not a number value?

Assuming a text value is acceptable, then if your sample data is representative, you could use the column F formula below.
If the text before the parentheses can vary in length then try the column G formula.

If numbers are wanted & sample representative, col H
If the text before the parentheses can vary in length then try col I

23 12 07.xlsm
	E	F	G	H	I
8	A (100.0%)	100.0%	100.0%	100	100
9	B (85.2%)	85.2%	85.2%	85.2	85.2
10
11	C (2%)	2%	2%	2	2
Extract Num

Cell Formulas
Range	Formula
F8:F11	F8	=SUBSTITUTE(MID(E8,4,20),")","")
G8:G11	G8	=IF(E8="","",REPLACE(LEFT(E8,LEN(E8)-1),1,FIND("(",E8),""))
H8:H11	H8	=IF(E8="","",-100*MID(E8,3,20))
I8:I11	I8	=IF(E8="","",-100*MID(E8,FIND("(",E8),20))

 

[kevin9999]

@2BLUE4U did the formula in post #6 not work for you?

 

[kevin9999]

Or if you wanted to return numerical values

=IF(A2<>"",MID(A2, SEARCH("(", A2)+1, SEARCH("%", A2) - SEARCH("(", A2) -1)*1,"")