Extract First or Last N Characters from String in Excel

[janema]

I am trying to only pull the percentage of column F, but is there a way to ensure it only pulls NUMBERS with the percentage sign?

For example: in the image shown below, the last 4 numbers brings in text to, but it should only bring in the numbers in front of the percentage. The only problem is that the percentage amounts vary between "5%" to "125%" so I am not sure if there is a formula that can pull in the correct percentage without extra text, spaces ,or missing data. This is a tough one for me. PLEASE HELP.

 

[awoohaw]

In 365 you can use

=1*TextBefore(TextAfter(F75,"Target "),"%")

In other versions try:

=Right(Substitute(F75,"%",""),Len(F75)-Find("Target ",F75)-7)

Book1
	F	G	H
75	adskjf jkdlfjlkasjdlf Target 5%	5	5
76	adskjf jkdlfjlkasjdlf Target 75%	75	75
77	adskjf jkdlfjlkasjdlf Target 125%	125	125
Sheet1

Cell Formulas
Range	Formula
G75:G77	G75	=1*RIGHT(SUBSTITUTE(F75,"%",""),LEN(F75)-FIND("Target ",F75)-7)
H75:H77	H75	=1*TEXTBEFORE(TEXTAFTER(F75,"Target "),"%")

 

[janema]

In 365 you can use

=1*TextBefore(TextAfter(F75,"Target "),"%")

In other versions try:

=Right(Substitute(F75,"%",""),Len(F75)-Find("Target ",F75)-7)

Book1
	F	G	H
75	adskjf jkdlfjlkasjdlf Target 5%	5	5
76	adskjf jkdlfjlkasjdlf Target 75%	75	75
77	adskjf jkdlfjlkasjdlf Target 125%	125	125
Sheet1

Cell Formulas
Range	Formula
G75:G77	G75	=1*RIGHT(SUBSTITUTE(F75,"%",""),LEN(F75)-FIND("Target ",F75)-7)
H75:H77	H75	=1*TEXTBEFORE(TEXTAFTER(F75,"Target "),"%")

That worked!!! Thank you so much!

 

[Peter_SSs]

Based on your sample data there are some considerably simpler formulas you could also use.

24 03 13.xlsm
	F	G	H
74		Earlier	365
75	adskjf jkdlfjlkasjdlf Target 5%	5	5
76	adskjf jkdlfjlkasjdlf Target 75%	75	75
77	adskjf jkdlfjlkasjdlf Target 125%	125	125
Extract number

Cell Formulas
Range	Formula
G75:G77	G75	=RIGHT(SUBSTITUTE(F75," ",REPT(" ",20)),20)*100
H75:H77	H75	=TEXTAFTER(F75," ",-1)*100

 

[Rick Rothstein]

Based on your sample data there are some considerably simpler formulas you could also use.

24 03 13.xlsm
	F	G	H
74		Earlier	365
75	adskjf jkdlfjlkasjdlf Target 5%	5	5
76	adskjf jkdlfjlkasjdlf Target 75%	75	75
77	adskjf jkdlfjlkasjdlf Target 125%	125	125
Extract number

Cell Formulas
Range	Formula
G75:G77	G75	=RIGHT(SUBSTITUTE(F75," ",REPT(" ",20)),20)*100
H75:H77	H75	=TEXTAFTER(F75," ",-1)*100

You could do the whole range with just a single formula (in 365)...

=TEXTAFTER(F75:F77," ",-1)*100

 

[awoohaw]

That worked!!! Thank you so much!

Happy to help. And there are other formulas that have been suggested that work as well. I even learned a new way to use some functions with some of them.

Best Wishes!