Numbers with percentage to be extracted from text

[ImranBhatti]

Respected Excellers there!

I have an excel file in which Column D contains a text string in which mentioned is the percentage of something as "If the employee registers so and so number of cases the percentage will be x%" . and these are many . So I need to extract the percentage in another column so that I may use it a specific formula.

Remember the position of my text in the string varies. I am not allowed to use macros in my sheet. is there any one cell formula to extract the percentage ?

[JohnTopley]

Please post a sample file showing a range (variability) of the data you have.

[Glenn Kennedy]

This should do it:
=RIGHT(SUBSTITUTE(LEFT(A1,FIND("%",A1)-1)," ",REPT(" ",255)),255)+0

[shukla.ankur281190]

Please Login or Register  to view this content.

Use this formula with Ctrl+Shift+Enter

Thanks
Ankur

[ImranBhatti]

Respected Excellers there!

I have an excel file in which Column D contains a text string in which mentioned is the percentage of something as "If the employee registers so and so number of cases the percentage will be x%" . and these are many . So I need to extract the percentage in another column so that I may use it a specific formula.

Remember the position of my text in the string varies. I am not allowed to use macros in my sheet. is there any one cell formula to extract the percentage ?

Find the attached testfile.

[Glenn Kennedy]

Sooo... if the data begins in c, then modify the formula to

=RIGHT(SUBSTITUTE(LEFT(C1,FIND("%",C1)-1)," ",REPT(" ",255)),255)+0

formatting the cells to General.

[JohnTopley]

Glenn has given you the solution.

[ImranBhatti]

Thanks Gelen....I think we are near to solution. please find the attached file in which I used your formula.but how can I remove the error .

[berlan]

Nice work Glenn!

If you want in percentage, format the output cells to show in percentage to (e.g. 0%) and use

=RIGHT(SUBSTITUTE(LEFT(C1,FIND("%",C1)-1)," ",REPT(" ",255)),255)%

[shukla.ankur281190]

Please Login or Register  to view this content.

What about this formula?

Thanks
Ankur

[JohnTopley]

=(RIGHT(SUBSTITUTE(LEFT(C1,FIND("%",C1)-1)," ",REPT(" ",255)),255)+0)/100 as a %

[Glenn Kennedy]

The answer to your Q is in the last line of Post 6

"formatting the cells to General". Currently, they are formatted as Percentage.

[ImranBhatti]

O I did not read your last instruction...and I wanted it with the percentage mark.I removed (-1) from your formula and it worked .The problem is solved thanks.

[ImranBhatti]

Berlan Your formula is also working.Thanks

[ImranBhatti]

Nice.This is also working according to my needs.thanks

[Glenn Kennedy]

Thanks for the Rep and glad to have helped. I was next door to you last week - in Islamabad with work!! Total luxury in the Serena hotel....

[ImranBhatti]

ankur thank you too,your formula is also producing the required result.

[ImranBhatti]

Glad to know that you'v been to my country.If I were at Serena then I would have hosted you myself.

[mohsinmkg23]

Hi Glenn, thank you for your solution. Saved my day!

Spent 15 minutes understanding your formula. Now I know a practical use case of SUBSTITUTE and REPT function. One thing I can't seem to understand is why use 255 in REPT function? Was there some reason or just an arbitrary number

[Glenn Kennedy]

Pretty much arbitrary...