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

 

[Peter_SSs]

Try this and format the column as % with 2 decimal places.

24 01 31.xlsm
	A	B
1	Staff costs
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%
Extract %

Cell Formulas
Range	Formula
B2:B9	B2	=--MID(A2,2,FIND("%",A2))

 

[Obewan]

Format as desired:
=--(SUBSTITUTE(A1,"% staff costs",""))

Thanks!!

 

[Peter_SSs]

Thanks!!

Just wondering how you get that formula to give 4.02% for the first result?

answer for the first one would = 4.02%

How did you format the result cell?

 

[Obewan]

Try this and format the column as % with 2 decimal places.

24 01 31.xlsm
	A	B
1	Staff costs
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%
Extract %

Cell Formulas
Range	Formula
B2:B9	B2	=--MID(A2,2,FIND("%",A2))

Thanks!!

Just wondering how you get that formula to give 4.02% for the first result?

How did you format the result cell?

yeah all good - thanks again!!

 

[Peter_SSs]

yeah all good - thanks again!!

Sorry, that does not answer my question.
I interpreted your response in post 12 to mean that Scott's formula was the one that gave you the answer you wanted. I'm not saying that it doesn't, but I couldn't see how it did and was interested to find out about it.

 

[Obewan]

I actually worked it out myself using this =IFERROR(TEXTAFTER(TEXTBEFORE(H41,"staff"),"$"),"N/a")

 

[Obewan]

I actually worked it out myself using this =IFERROR(TEXTAFTER(TEXTBEFORE(H41,"staff"),"$"),"N/a")

Then I added this to extract the % and turn into a figure =TEXTBEFORE(N2,"%")/100

 

[Obewan]

Then I added this to extract the % and turn into a figure =TEXTBEFORE(N2,"%")/100

actually this works better - =IFERROR(TEXTAFTER(TEXTBEFORE(H2,"%"),"$"),"N/a")/100 the issue was the "%" was a text value

 

[Peter_SSs]

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

actually this works better - =IFERROR(TEXTAFTER(TEXTBEFORE(H2,"%"),"$"),"N/a")/100 the issue was the "%" was a text value

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")

 

[Scott Huish]

Maybe better:
=SUBSTITUTE(A1,"% staff costs","")/100

Format as % with 2 decimal places

Book1
	A	B
1	Staff Costs
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%
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=SUBSTITUTE(A2,"% staff costs","")/100