Extract a number from a string

[richiwatts]

I have a column like this.

Quality Score: 5 (20180412)
Quality Score: 5 (20180220)
Quality Score: 4 (xxxxxxxx)
Quality score: 4 (20180713)
Quality Score; 4 (20180605)

I need to get just the scores in a different column so I can average them in a pivot base don toher criteria I have in other columns.

I was able to get the score in a new column using
=IFERROR(MID(F8,16,LEN(F8)-26))
But then it is not a number it is text so I can't get an average. I tried formatting the numbering but that didn't work.
Can you think of a way I can get just the number? The data is constantly updating so I can't just search and replace, I need a formula or something.

Thanks

 

[Derek Brown]

First you need an extra argument to the formula:
Try:
=IFERROR(MID(F8,16,LEN(F8)-26),0)
The following should convert the result to a number:
=IFERROR(MID(F8,16,LEN(F8)-26),0)*1

 

[jtakw]

Hi,

There are many ways to convert the extracted results to numeric, using either *1, +0, --, etc.

But I would definitely recommend adding that Before the "value if error", if it's added After "value if error", it would just error out again.

Book1
	F	G	H
8	Quality Score: 5 (20180412)	5	5
9	Quality Score: 5 (20180220)	5	5
10	Quality Score: 4 (xxxxxxxx)	4	4
11	Quality score: 4 (20180713)	4	4
12	Quality Score: 4 (20180605)	4	4
13	Quality Score: A (20180605)	0	#VALUE!
Sheet137

Cell Formulas
Range	Formula
G8	=IFERROR(MID(F8,16,LEN(F8)-26)+0,0)
H8	=IFERROR(MID(F8,16,LEN(F8)-26),0)*1

Use G8 formula, adjust cell reference as needed, change the 0 (zero - After the Last comma) for "value if error" to "" (Blank) or whatever you want.

 

[richiwatts]

Thanks both, this was what I was looking for.