Extract text of varying length from middle of an equation

[phmalu]

Hi
I have some quadratic equations and I must automatically extract the values of A, B and C to different cells. The problem is that all of them vary in length constantly so I cannot use the =LEFT formula. I was thinking about using the number of spaces between each of the arguments but I don't know how to do that. A is always between the second space and the first X; B is always between the forth space and the second X; and finally C is everything after the last (sixth) space. Please help with the formula.

Examples:

y = 0,1502x² - 0,3883x + 1,648 (A= 0,1502; B= 0,3883; C=1,648)

y = 0,2236x² - 1,5631x + 24,746

y = -531,62x² + 196,71x + 18,815

y = -151,15x² + 27,609x + 21,805

y = 8,0870x² + 11,317x + 21,515

y = -17,795x² + 34,356x + 27,892 Thank you.

<tbody>
</tbody>

 

[Rick Rothstein]

These would work...

A: =0+MID(LEFT(A1,FIND("x",A1)-1),5,99)

B: =0+SUBSTITUTE(MID(LEFT(A1,FIND("x",A1,FIND("x",A1)+1)-1),FIND("x",A1)+2,99)," ","")

C: =0+RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)

This one is a little more compact for the "B" coefficient...

=0+SUBSTITUTE(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),300,200)),"x","")

 

[phmalu]

These would work...

A: =0+MID(LEFT(A1,FIND("x",A1)-1),5,99)

B: =0+SUBSTITUTE(MID(LEFT(A1,FIND("x",A1,FIND("x",A1)+1)-1),FIND("x",A1)+2,99)," ","")

C: =0+RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99)

Works flawlessly
Why did you add "0+" in the beginning of the formula? What does it mean when you add double quotes without anything inside? Like:
B: =0+SUBSTITUTE(MID(LEFT(A1,FIND("x",A1,FIND("x",A1)+1)-1),FIND("x",A1)+2,99)," ","")
I'm sorry for asking so many questions but that's because I'm trying to learn everything I can possibly learn so I can start making my own advanced formulas without teasing others. I'm an intermediate user trying to become more advanced.
Thank you!

 

[Rick Rothstein]

Works flawlessly
Why did you add "0+" in the beginning of the formula? What does it mean when you add double quotes without anything inside? Like:
B: =0+SUBSTITUTE(MID(LEFT(A1,FIND("x",A1,FIND("x",A1)+1)-1),FIND("x",A1)+2,99)," ","")
I'm sorry for asking so many questions but that's because I'm trying to learn everything I can possibly learn so I can start making my own advanced formulas without teasing others. I'm an intermediate user trying to become more advanced.

The SUBSTITUTE function returns a text value, even if that text looks like a number. Whenever you involve a text value that looks like a number in a mathematical operation, Excel converts that text number to a real number in order to perform the calculation. Adding zero to any number will not change its value, so adding zero to the text value returned by the SUBSTITUTE function has the effect of converting it to a real number. As for the double quotes ("")... that is a text string with no characters in it (sometime called the empty string). What it is being used for in my formula is to eliminate a blank space. Let's say your equation was this...

y = 12x² - 34x + 56

The "B" formula pulls this text number out (notice there is a space between the two parts)...

- 34

and delivers it to the SUBSTITUTE function. I mistakenly (as it turn out) thought that the space would prevent Excel from seeing that value as a number, so I used the SUBSTITUTE function to substitute the empty string for the space meaning this text number would be returned...

-34

so that when zero was added to it, it would become a real number. However, I just checked and discovered the space between the sign and the number does not matter to Excel, so you can just use this formula to obtain the "B" coefficient...

B: =0+MID(LEFT(A1,FIND("x",A1,FIND("x",A1)+1)-1),FIND("x",A1)+2,99)

 

[phmalu]

The SUBSTITUTE function returns a text value, even if that text looks like a number. Whenever you involve a text value that looks like a number in a mathematical operation, Excel converts that text number to a real number in order to perform the calculation. Adding zero to any number will not change its value, so adding zero to the text value returned by the SUBSTITUTE function has the effect of converting it to a real number. As for the double quotes ("")... that is a text string with no characters in it (sometime called the empty string). What it is being used for in my formula is to eliminate a blank space. Let's say your equation was this...

y = 12x² - 34x + 56

The "B" formula pulls this text number out (notice there is a space between the two parts)...

- 34

and delivers it to the SUBSTITUTE function. I mistakenly (as it turn out) thought that the space would prevent Excel from seeing that value as a number, so I used the SUBSTITUTE function to substitute the empty string for the space meaning this text number would be returned...

-34

so that when zero was added to it, it would become a real number. However, I just checked and discovered the space between the sign and the number does not matter to Excel, so you can just use this formula to obtain the "B" coefficient...

B: =0+MID(LEFT(A1,FIND("x",A1,FIND("x",A1)+1)-1),FIND("x",A1)+2,99)

We have some excellent explanations here, thank you once again
I'm not very familiar with the SUBSTITUTE function as I've never used it before. I think I must read more details about it and perhaps experiment a little with it. Then I might contact you once again, but for now I believe all my questions have been answered.
I really appreciate your willingness to help me

 

[Rick Rothstein]

We have some excellent explanations here, thank you once again
I'm not very familiar with the SUBSTITUTE function as I've never used it before. I think I must read more details about it and perhaps experiment a little with it. Then I might contact you once again, but for now I believe all my questions have been answered.
I really appreciate your willingness to help me

You are quite welcome. Just checking to make sure what I wrote in Message #13 was clear... you can use the formula I posted at the bottom of that message for the "B" coefficient in place of the SUBSTITUTE formula you asked about.

 

[phmalu]

You are quite welcome. Just checking to make sure what I wrote in Message #13 was clear... you can use the formula I posted at the bottom of that message for the "B" coefficient in place of the SUBSTITUTE formula you asked about.

Roger that, thank you for clearing things up
However, I think it's a good idea to read more about the SUBSTITUTE formula as I think it might be useful in the future.
If you're aware of any topic/tutorial that explains more about it and how to use it correctly please let me know. It could be very helpful.

 

[Rick Rothstein]

However, I think it's a good idea to read more about the SUBSTITUTE formula as I think it might be useful in the future.
If you're aware of any topic/tutorial that explains more about it and how to use it correctly please let me know. It could be very helpful.

The Excel help files is a great place to start. For any formula we post here where you have question about one of the functions used in it, put the formula in a cell, then click anywhere within its parentheses... a box will popup showing the function name and its argument list... hover the mouse over the function name within that popup box and the function name will turn blue... click it and the help file for that function will appear.

 

[phmalu]

The Excel help files is a great place to start. For any formula we post here where you have question about one of the functions used in it, put the formula in a cell, then click anywhere within its parentheses... a box will popup showing the function name and its argument list... hover the mouse over the function name within that popup box and the function name will turn blue... click it and the help file for that function will appear.

I do remember checking out the help files more than once. Even though they can be quite useful, they are not always "user-friendly". Sometimes it's hard to understand what they are trying to say, at least for me. This is way I'd rather visit another site, either in English or Portuguese (I'm a native Portuguese speaker), which uses less complex vocabulary. Thanks anyways, I will make sure to take note and give it a try once I have any other questions regarding new functions.