Formulat to return certain text from sentence

[thelad]

Hi,

I have the following sentence in A1

"ID1: The change is 0.15% last change is 0.54% (Recommended)"

In B1 I would like formula return the percentage in between "is" and "Last". So this would be 0.15%

In C1 I would like formula return the percentage at the end. Between "is" and "recommended". So this would be 0.54%

The word recommended can change so cannot really do this.

An ideas?

 

[Gerald Higgins]

Can we use a rule of finding the first instance of the word "is", and the second instance of the same word, to identify the position of the numbers ?
Do you want to return the numbers as text strings, or as numbers that you can then perform calculations on, via formulas ?
Will the numbers always be in the same format, i.e. 9.99%, and never 99.9% or 9,999%, or 9,999.999%, and so on ?

 

[devilishblueyes]

My suggestion would be to use the FIND and MID functions. Then you can slowly piece together the formula by building segments or portions of the formula, then combining the segments together and then simplifying it.

First, I would use the FIND formula to find the word "is" in the text.

=FIND("is", A1)

That's going to give you a result of 17. That means that the character "i" in the first instance "is" was the 17th character. That makes the first number of 0.15% the 20th character.

So the formula for the first character of that number would be:

=FIND("is", A1) + 3

To find the last character of it, you can use the same find formula to find the % symbol.

=FIND("%", A1)

Note that I'm leaving the start number out. This assumes a start number of 1. This formula will give a result of 24.

When we subtract our start position from our end position we have to add 1 to it to get the correct number of characters for the length of the string.

For example:

24 - 20 = 4, so we have to add 1 to get 5 characters

24 - 20 +1 = 5, giving us the correct length

We then can start combining these formulas into the MID formula which returns the text that we want.

=MID(A1,FIND("is",A1)+3,(FIND("%",A1)-(FIND("is",A1)+3)+1))

You can do pretty much the same process to find the second number. But for the second number you will want to add a start number to find the second iteration of "is".

=FIND("is", A1, FIND("is", A1) + 1)

Basically what we are telling Excel is to find the 2nd instance of the word "is" in the string. We state that the starting point is 1 more character than the first instance of the first word "is".

You then can use the previous processes to figure out how to get the rest of the formula.

 

[thelad]

Hi,

First formula worked, however I noticed there is no "is" in last part.
It's:

last change 0.54%(recommended)

I tried the following formula and it did not work. any ideas

 

[thelad]

=MID(E2,FIND("change",E2)+3,(FIND("%",E2)-(FIND("change",E2)+3)+1))

 

[jtakw]

Hi,

Format result cells as "percentage":

Book1
	A	B	C
1	ID1: The change is 0.15% last change is 0.54% (Recommended)	0.15%	0.54%
2	ID1: The change is 0.15% last change 0.54% (Recommended)	0.15%	0.54%
3	ID1: The change 50.15% last change 90.54%	50.15%	90.54%
Sheet588

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