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

 

[dreid1011]

Maybe this:

=TEXTAFTER(TEXTBEFORE(A2," "),"$")

 

[Obewan]

Maybe this:

=TEXTAFTER(TEXTBEFORE(A2," "),"$")

Wow that was quick - looks good but still has the word staff in the answer - i.e. answer = 4.02% staff - can we get rid of the word staff as well?

 

[dreid1011]

It would only have "staff" in the result if there is not a space between "%" and "s". The solution I provided requires "% s". If adding the space is not desired, then change the formula to this:

=TEXTAFTER(TEXTBEFORE(A2,"staff"),"$")

 

[Coyotex3]

Maybe this option will work

=TEXTAFTER(TEXTBEFORE(A2," "),"$")

Edit: Just realized this is the same answer @dreid1011 posted initially!

 

[Obewan]

It would only have "staff" in the result if there is not a space between "%" and "s". The solution I provided requires "% s". If adding the space is not desired, then change the formula to this:

=TEXTAFTER(TEXTBEFORE(A2,"staff"),"$")

Champion!! Thanks a million!

 

[dreid1011]

You're welcome, and happy to help.

 

[Obewan]

You're welcome, and happy to help.

One more thing - the answer is good but doesn't appear to be in the right format - how can I get it into a number or % format? Think it might be in text or something as I can't write any formulas to get the median or average?

 

[Coyotex3]

You could try adding a -- in front of the formula to convert it into a number.

 

[Scott Huish]

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