Pulling out certain characters from a cell

[andreascostas]

I have this equation in cell a1: 15x² + 13x – 20
I want to pull out the coefficients and the constant in three separate cells.
I use =LEFT(a1,FIND("x",a1)-1) to pull out the 15…..I am having trouble pulling
Out the 13 and the 20
Help please
The function should work with other similar equations such as: 9x² - 27x + 20

 

[Beyond_avarice]

Just quick and dirty:

=ABS(MID(A1,FIND("x",A1,4)-3,3))

=ABS(RIGHT(A1,3))

This will only work if you have your second coefficient and constant as no more than three characters.

 

[Peter_SSs]

1. Do you always have all three types of terms in the equation?

2. Are there always spaces surrounding the + or - signs between the terms like in your one example?

3. You say you are having trouble pulling out the 20 from your equation. Are you actually trying to extract 20 in that example, or should it be -20?

If there is considerable variation in the data, could you show more examples of that variation and the expected results?

 

[Rick Rothstein]

I have this equation in cell a1: 15x² + 13x – 20
I want to pull out the coefficients and the constant in three separate cells.
I use =LEFT(a1,FIND("x",a1)-1) to pull out the 15…..I am having trouble pulling
Out the 13 and the 20
Help please

A couple of questions...

1) What if a coefficient is 0... do you show that or omit the term? For example, which of these would be in your cell...

15x² + 0x – 20

or

15x² – 20



2) Is the exponent a normal 2 (either formatted as a superscript or not) or is it this character ² (ASCII 178)?

 

[andreascostas]

Yes, you are correct, the if the constant is negative I need the negative sign along with the constant. Likewise for the second coefficient if it has a minus sign in front of it.
Yes, all three types of terms are in the equation
Yes , there are always spaces surrounding the + or the - sins between the terms.
I am trying to create worksheets from my students. On the left I have the equation. I feed in the coefficients and constant into into a solver and it gives me the roots.
Thanks

 

[andreascostas]

I show it as 15x² + 0x – 20.....the exponent is 2 that I turn to a superscript

 

[Rick Rothstein]

Here is one way to do it... Assuming your equation is in cell A1, put this formula in cells B1 and copy it across to D1, then copy those three cells down to the bottom of your data (assuming you have more than one row of data)...

=0+TRIM(MID(SUBSTITUTE("@"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1," ",""),"x2","@"),"x","@"),"+",""),"@",REPT(" ",100)),COLUMNS($B1:B1)*100,100))

 

[Peter_SSs]

Yes, you are correct, the if the constant is negative I need the negative sign along with the constant. Likewise for the second coefficient if it has a minus sign in front of it.
Yes, all three types of terms are in the equation
Yes , there are always spaces surrounding the + or the - sins between the terms.

In that case you could try these 3 formulas, copied down.

Edit: I have assumed the coefficients are always whole numbers. If not, safer for the formula in C2 below to be changed to
=RIGHT(SUBSTITUTE(LEFT(A2,FIND("x",A2,FIND(" ",A2))-1)," ",REPT(" ",20)),40)+0

Excel 2016 (Windows) 32 bit
	A	B	C	D
1		x2	x	Const
2	15x2 + 13x - 20	15	13	-20
3	- 20x2 + 0x + 3000	-20	0	3000
4	- 20x2 + 20x + 0	-20	20	0
5	0x2 + 3x + 2	0	3	2
Coefficients

Cell Formulas
Range	Formula
B2	=LEFT(A2,FIND("x",A2)-1)+0
C2	=RIGHT(SUBSTITUTE(LEFT(A2,FIND("x",A2,LEN(B2)+3)-1)," ",REPT(" ",20)),40)+0
D2	=RIGHT(SUBSTITUTE(A2," ",REPT(" ",20)),40)+0

 

[Rick Rothstein]

Here is one way to do it... Assuming your equation is in cell A1, put this formula in cells B1 and copy it across to D1, then copy those three cells down to the bottom of your data (assuming you have more than one row of data)...

=0+TRIM(MID(SUBSTITUTE("@"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1," ",""),"x2","@"),"x","@"),"+",""),"@",REPT(" ",100)),COLUMNS($B1:B1)*100,100))

Here is a simple set of formulas... Again, assuming your equation is in cell A1, put these formulas in the indicated cells and copy them down...

B1: =0+LEFT(A1,FIND("x2",A1)-1)

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

D1: =0+MID(A1,FIND("x ",A1)+2,99)

 

[andreascostas]

Wow, this worked on all cases except where the second coefficient was 1 as in [TABLE="width: 141"]
<colgroup><col width="141" style="width:106pt"> </colgroup><tbody>[TR]
[TD="class: xl87, width: 141"]12x² + x - 6[/TD]
[/TR]
</tbody>[/TABLE]
I can always do those by hand...Thank you for your help.