Extracting text before a specific character and deleting the rest

[DHLITTLE]

I have a text field for example:

There are 8% of people who wear red

I'd like to extract whatever number is before the "%" sign.

I got as far as [FONT=&quot]=MID([/FONT][FONT=&quot]AJ2[/FONT][FONT=&quot],FIND[/FONT][FONT=&quot]([/FONT][FONT=&quot]"%",[/FONT][FONT=&quot]AJ2[/FONT][FONT=&quot])[/FONT][FONT=&quot]-3,LEN[/FONT][FONT=&quot]([/FONT][FONT=&quot]AJ2[/FONT][FONT=&quot])[/FONT]) which kind of works but leaves everything after the % sign. I just want the number ideally.

Also some of the numbers are 12.3% others might be 1% so simply extracting the 3 characters before the % sign doesn't work reliably.

Is there a better way of doing this?

Thanks in advance.

 

[dreid1011]

Welcome to the board. Perhaps this:

=TRIM(MID(SUBSTITUTE(AJ2," ",REPT(" ",99)),FIND("%",SUBSTITUTE(AJ2," ",REPT(" ",99)))-5,6))

So long as there is only one % in each string. Also, change the 6 to 5 if you don't want to include the %.

 

[jtakw]

Hi,

Another way, result converted to Real Number in case of further comparison and/or math:

Also, this formula Won't fail in case the 8% is at the beginning of the Text string.

Book1
	A	B
1	There are 8% of people who wear red	8
2	8% of people wear red	8
3	People who wear read is 8%	8
Sheet69

Cell Formulas
Range	Formula
B1	=RIGHT(SUBSTITUTE(LEFT(A1,FIND("%",A1)-1)," ",REPT(" ",15)),15)+0

 

[dreid1011]

In light of jtakw's findings, here is a slightly modified version that will not fail if the % is at the beginning of the string. And a version converting to number for calculations.

=TRIM(MID(SUBSTITUTE(AJ2," ",REPT(" ",99)),MAX(1,FIND("%",SUBSTITUTE(AJ2," ",REPT(" ",99)))-4),5))

=TRIM(MID(SUBSTITUTE(AJ2," ",REPT(" ",99)),MAX(1,FIND("%",SUBSTITUTE(AJ2," ",REPT(" ",99)))-4),5))+0

 

[DHLITTLE]

In light of jtakw's findings, here is a slightly modified version that will not fail if the % is at the beginning of the string. And a version converting to number for calculations.

=TRIM(MID(SUBSTITUTE(AJ2," ",REPT(" ",99)),MAX(1,FIND("%",SUBSTITUTE(AJ2," ",REPT(" ",99)))-4),5))

=TRIM(MID(SUBSTITUTE(AJ2," ",REPT(" ",99)),MAX(1,FIND("%",SUBSTITUTE(AJ2," ",REPT(" ",99)))-4),5))+0

Thank you both for your help, it is incredibly helpful.

 

[dreid1011]

You're welcome. Thanks for the feedback.

 

[jtakw]

Thank you both for your help, it is incredibly helpful.

You're welcome, welcome to the forum.

Remove the -1 within my formula in Post #3 if you want the %, format result cells as "percentage":

Book1
	A	B	C
1	There are 8% of people who wear red	8	8%
2	8% of people wear red	8	8%
3	People who wear read is 8%	8	8%
Sheet69

Cell Formulas
Range	Formula
B1	=RIGHT(SUBSTITUTE(LEFT(A1,FIND("%",A1)-1)," ",REPT(" ",15)),15)+0
C1	=RIGHT(SUBSTITUTE(LEFT(A1,FIND("%",A1))," ",REPT(" ",15)),15)+0