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

 

[Rick Rothstein]

I disagree that this produces the form indicated by the OP. You pointed out that the OP wants the 0 coefficients stated explicitly, but by their example (post #10), coefficients of "1" are omitted.

I forgot the other SUBSTITUTE.

=SUBSTITUTE(SUBSTITUTE(N(A1)&"x"&CHAR(178)&" + "&N(B1)&"x + "&N(C1),"+ -","- ")," 1x"," x")

 

[StephenCrump]

Posted in error

 

[Rick Rothstein]

I forgot the other SUBSTITUTE.

=SUBSTITUTE(SUBSTITUTE(N(A1)&"x"&CHAR(178)&" + "&N(B1)&"x + "&N(C1),"+ -","- ")," 1x"," x")

Of course, the above formula is not exactly right either. This should be though...

=TRIM(SUBSTITUTE(SUBSTITUTE(" "&SUBSTITUTE(N(A1)&"x"&CHAR(178)&" + "&N(B1)&"x + "&N(C1),"+ -","- ")," 1x"," x"),"-1x"&CHAR(178),"-x"&CHAR(178)))

 

[StephenCrump]

=SUBSTITUTE(SUBSTITUTE(N(A1)&"x"&CHAR(178)&" + "&N(B1)&"x + "&N(C1),"+ -","- ")," 1x"," x")

To suppress +/1x², perhaps:

=TRIM(SUBSTITUTE(SUBSTITUTE(" "&N(A1)&"x"&CHAR(178)&" + "&N(B1)&"x + "&N(C1),"+ -","- ")," 1x"," x"))

Edit: Rick, you just beat me to it. But a quick look at your formula suggests you've identified another issue?

 

[Rick Rothstein]

Edit: Rick, you just beat me to it. But a quick look at your formula suggests you've identified another issue?

A leading -1x² needs to become -x²

 

[Rick Rothstein]

Of course, the above formula is not exactly right either. This should be though...

=TRIM(SUBSTITUTE(SUBSTITUTE(" "&SUBSTITUTE(N(A1)&"x"&CHAR(178)&" + "&N(B1)&"x + "&N(C1),"+ -","- ")," 1x"," x"),"-1x"&CHAR(178),"-x"&CHAR(178)))

I think the following also works and it is a smidge "tighter"...

=SUBSTITUTE(MID(SUBSTITUTE(SUBSTITUTE("+ "&N(A1)&"x"&CHAR(178)&" + "&N(B1)&"x + "&N(C1),"-1x","-x"),"+ 1x","+ x"),3,99),"+ -","- ")

Edit Note: Keep in mind that with the exception of Messages 16, 18, 19 and 22, all of the messages back to Message #13 are responses to StephenCrump's suggestion in Message #13.