formula to extract numbers from text

[Obewan]

I want a formula to extract the % numbers from the text in column B - data below is from column A. Answer can just be a number or % format. For example answer for the first one would = 4.02%. Thanks in advance!!

Staff costs

$4.02% staff costs

$73.31% staff costs

$60.60% staff costs

$59.94% staff costs

$74.16% staff costs

$11.47% staff costs

$53.33% staff costs

$100.90% staff costs

 

[Obewan]

Thanks for the explanation & glad you worked it out. However ..


I don't think so. I think you would need the IFEEROR around the whole lot, not just before the /100
The two versions shown in I:J below.
Also, what about the somewhat simpler version offered before, but now including the IFERROR, in col K below

24 01 31.xlsm
	H	I	J	K
1
2	$4.02% staff costs	4.02%	4.02%	4.02%
3	$73.31% staff costs	73.31%	73.31%	73.31%
4	$60.60% staff costs	60.60%	60.60%	60.60%
5	$59.94% staff costs	59.94%	59.94%	59.94%
6	$74.16% staff costs	74.16%	74.16%	74.16%
7	$11.47% staff costs	11.47%	11.47%	11.47%
8	$53.33% staff costs	53.33%	53.33%	53.33%
9	$100.90% staff costs	100.90%	100.90%	100.90%
10		#VALUE!	N/a	N/a
Extract %

Cell Formulas
Range	Formula
I2:I10	I2	=IFERROR(TEXTAFTER(TEXTBEFORE(H2,"%"),"$"),"N/a")/100
J2:J10	J2	=IFERROR(TEXTAFTER(TEXTBEFORE(H2,"%"),"$")/100,"N/a")
K2:K10	K2	=IFERROR(--MID(A2,2,FIND("%",A2)),"N/a")

Yeah it's weird - the one in K just gives me "N/a" for everything.

 

[Peter_SSs]

the one in K just gives me "N/a" for everything.

Sorry, a couple of things happened there

1. I forgot to change the cell references from column A to column H in my formula, and
2. I forgot to allow for the fact that you seem to have a non-standard space character between the % sign and the word staff (I'm guessing CHAR(160))

To at least satisfy my curiosity could you try this one please?

24 01 31.xlsm
	H			K
1
2	$4.02% staff costs			4.02%
3	$73.31% staff costs			73.31%
4	$60.60% staff costs			60.60%
5	$59.94% staff costs			59.94%
6	$74.16% staff costs			74.16%
7	$11.47% staff costs			11.47%
8	$53.33% staff costs			53.33%
9	$100.90% staff costs			100.90%
10				N/a
Extract %

Cell Formulas
Range	Formula
K2:K10	K2	=IFERROR(--MID(H2,2,FIND("%",H2)-1),"N/a")

 

[Scott Huish]

Sorry, a couple of things happened there

1. I forgot to change the cell references from column A to column H in my formula, and
2. I forgot to allow for the fact that you seem to have a non-standard space character between the % sign and the word staff (I'm guessing CHAR(160))

To at least satisfy my curiosity could you try this one please?

I changed mine to divide by 100, so that it actually returned a percentage, but you don't need to use MID, Excel will recognize the number despite the $ sign

Since you're allowing for a non-breaking space ,perhaps:
=IFERROR(LEFT(H2,FIND("%",H2)-1)/100,"N/a")

 

[Peter_SSs]

I changed mine to divide by 100, so that it actually returned a percentage, but you don't need to use MID, Excel will recognize the number despite the $ sign

Instead of excluding the % sign & recognising the number despite the $ sign then converting to a percentage by dividing by 100 I deliberately used mid to avoid the $ sign but include the % & coerced to a number with Excel then recognising it not only as a number but as a percentage.
Six of one and half a dozen of the other I guess.

 

[Obewan]

Sorry, a couple of things happened there

1. I forgot to change the cell references from column A to column H in my formula, and
2. I forgot to allow for the fact that you seem to have a non-standard space character between the % sign and the word staff (I'm guessing CHAR(160))

To at least satisfy my curiosity could you try this one please?

24 01 31.xlsm
	H			K
1
2	$4.02% staff costs			4.02%
3	$73.31% staff costs			73.31%
4	$60.60% staff costs			60.60%
5	$59.94% staff costs			59.94%
6	$74.16% staff costs			74.16%
7	$11.47% staff costs			11.47%
8	$53.33% staff costs			53.33%
9	$100.90% staff costs			100.90%
10				N/a
Extract %

Cell Formulas
Range	Formula
K2:K10	K2	=IFERROR(--MID(H2,2,FIND("%",H2)-1),"N/a")

Bingo!! Thanks for discussing with me. I will learn from your formula!!

 

[Peter_SSs]

You're welcome. Glad to contribute.