Extracting a number with decimal and percentage from a text string

[Jansiej]

Hi,

Could anyone please assist with a formula. I am trying to extract a number with decimal and percentage sign from a stringed text.

As example it would read.

Cell: B243

We have decided to apply a rate of 10.5% in our calculations as this is deemed realistic.

The text string might change but there will always be a number with decimal and percentage in the sentence.

I need to extract only the 10.5% exactly as indicated in the sentence. I have tried a few formulas after using Google but have not managed to get it right.

Thank you so much!

Regards Jansie

 

[Caribeiro77]

Hi and welcome to the board...
The celll is B243, but what exactly does the cell contains? What's the string?

 

[AhoyNC]

See if this will work.
B3 as text
B4 as number (format to %).

Excel Workbook
	A	B	C
1	We have decided to apply a rate of 10.5% in our calculations as this is deemed realistic.
2
3	rate	10.5%	As text
4		10.50%	As number
Sheet

 

[Jansiej]

Thanks Caribeiro77

Hi and welcome to the board...
The celll is B243, but what exactly does the cell contains? What's the string?

The text string will read something like "We have decided to apply a rate of 10.5% in our calculations as this is deemed realistic."

Thank You

 

[Myrko]

I believe the value of B243 should be: "We have decided to apply a rate of 10.5% in our calculations as this is deemed realistic." and 10.5% needs to be found and extracted from that text. I have an idea how to make it work only on texts containing % in that format (it wouldn't work if it says 10.5 % - space between number and sign for instance). If you know that % will always stand right next to number I can fix it.

 

[Rick Rothstein]

Here is another formula for you to consider...

As text: =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("%",A1))," ",REPT(" ",200)),200))

As number: =0+TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("%",A1))," ",REPT(" ",200)),200))

If you use the "as number" formula, you will have to format the cell as Percentage.

 

[AhoyNC]

Rick,
If there should be a space between the number and % sign (10.5 %) your formula seems to fail, but it is simpler and better than mine in this case.

 

[Jansiej]

Thanks so much AhoyNC!!!

That did the trick! Wow saves me a lot of time.

Cheers

 

[Jansiej]

Thanks Rick

This one also works like a charm!

Regards

 

[zsoni]

Hi,

the formula works like a magic. I have 2 numbers with % sign and I was able to extract 1st % sign number with this formula BUT now I want to extract the 2nd number with a % sign from the same cell and put them in a different cell. How do I do it?