# Formulat to return certain text from sentence

##### Board Regular
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?

### Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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 ?

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.

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

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

Hi,

Format result cells as "percentage":

Book1
ABC
1ID1: The change is 0.15% last change is 0.54% (Recommended)0.15%0.54%
2ID1: The change is 0.15% last change 0.54% (Recommended)0.15%0.54%
3ID1: The change 50.15% last change 90.54%50.15%90.54%
Sheet588
Cell Formulas
RangeFormula
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

Replies
0
Views
78
Replies
7
Views
434
Replies
4
Views
153
Replies
19
Views
762
Replies
0
Views
174

### Forum statistics

1,196,152
Messages
6,013,742
Members
441,781
Latest member
Gian Carlos ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back