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]

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

 

[dreid1011]

You're welcome, and happy to help.

 

[Scott Huish]

Format as desired:
=--(SUBSTITUTE(A1,"% 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))

 

[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.

 

[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?

 

[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!