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]

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.

No need to do them by hand... see Message #9 (if that is the one you are responding to, note that I edited the formula for cell C1 to work with the implied coefficient of 1).

 

[andreascostas]

Thank You both for your help

 

[StephenCrump]

I'd be tempted to turn this around ... much easier and faster to type numbers into cells than edit strings with superscripts?

For more normal looking equations, e.g. x² + 4 rather than 1x² + 0x + 4, the formula will be a bit more cumbersome:

D1: =IF(A1=0,"",IF(SIGN(A1)=1,"","-")&IF(ABS(A1)<>1,ABS(A1),"") & "x" & CHAR(178)&" ")&IF(B1=0,"",IF(SIGN(B1)=1,IF(A1=0,"","+ "),"- ")&IF(ABS(B1)<>1,ABS(B1),"") & "x ")&IF(C1=0,"",IF(SIGN(C1)=1,IF(COUNTIF(A1:B1,0)<>2,"+ ",""),"- ")&ABS(C1))

Excel 2010
	A	B	C	D
1	1	1	1	x + x + 1
2	-1.2	0.8	0	-1.2x + 0.8x
3	1		4	x + 4
Sheet1

In present company, I suspect someone will shorten this.

 

[Rick Rothstein]

I'd be tempted to turn this around ... much easier and faster to type numbers into cells than edit strings with superscripts?

For more normal looking equations, e.g. x² + 4 rather than 1x² + 0x + 4, the formula will be a bit more cumbersome:

D1: =IF(A1=0,"",IF(SIGN(A1)=1,"","-")&IF(ABS(A1)<>1,ABS(A1),"") & "x" & CHAR(178)&" ")&IF(B1=0,"",IF(SIGN(B1)=1,IF(A1=0,"","+ "),"- ")&IF(ABS(B1)<>1,ABS(B1),"") & "x ")&IF(C1=0,"",IF(SIGN(C1)=1,IF(COUNTIF(A1:B1,0)<>2,"+ ",""),"- ")&ABS(C1))

Excel 2010
	A	B	C	D
1	1	1	1	x + x + 1
2	-1.2	0.8	0	-1.2x + 0.8x
3	1		4	x + 4
Sheet1

In present company, I suspect someone will shorten this.

You cannot superscript the 2 if the output is generated by a formula... any one character in the text resulting from a formula can only have the same format as all of the other characters in the cell (this is an Excel restriction). It can be done with VBA code if that would be okay with the OP.

 

[StephenCrump]

You cannot superscript the 2 if the output is generated by a formula...

True ... which is why I used CHAR(178).

 

[StephenCrump]

I assume we're playing only in real numbers, so turning around could also simplify the generation of well-behaved equations, e.g.

D2: =IF(INT(SQRT(C2))=SQRT(C2),(-A2+SQRT(C2))/B2,"("& A2 & "+ SQRT(" & C2 &"))/"&B2)
E2: =IF(INT(SQRT(C2))=SQRT(C2),(-A2-SQRT(C2))/B2,"("& A2 & "- SQRT(" & C2 &"))/"&B2)
F2: =B2/2
G2: =-A2
H2: =-(C2-G2^2)/(4*F2)
I2: =IF(F2=0,"",IF(SIGN(F2)=1,"","-")&IF(ABS(F2)<>1,ABS(F2),"") & "x" & CHAR(178)&" ")&IF(G2=0,"",IF(SIGN(G2)=1,IF(F2=0,"","+ "),"- ")&IF(ABS(G2)<>1,ABS(G2),"") & "x ")&IF(H2=0,"",IF(SIGN(H2)=1,IF(COUNTIF(F2:G2,0)<>2,"+ ",""),"- ")&ABS(H2))

Excel 2010
	A	B	C	D	E	F	G	H	I
1	-b	2a	b2 - 4ac	Root1	Root2	a	b	c	Formula
2	3	2	49	2	-5	1	-3	-10	x - 3x - 10
3	2	2	2	(2+ SQRT(2))/2	(2- SQRT(2))/2	1	-2	0.5	x - 2x + 0.5
4	0	2	16	2	-2	1	0	-4	x - 4
Sheet1

 

[Rick Rothstein]

i'd be tempted to turn this around ... Much easier and faster to type numbers into cells than edit strings with superscripts?

excel 2010

a b c d
1 1 1 1 x² + x + 1
2 -1.2 0.8 0 -1.2x² + 0.8x
3 1
4 x² + 4

True ... which is why I used CHAR(178).

I guess I should have read your formula more closely Sorry.

I would point out, however, that if the coeficient is 0, the OP said he wants to display it (see Message #6).

Using your CHAR(178) trick to put the exponent into the equation, the following formula should take the three value in Columns A, B and C and create an equation in the format the OP has indicated he wants...

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

 

[Peter_SSs]

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]

It is interesting that when the coefficient of the "x" term is zero, you explicitly include the 0 coefficient in your formula, but when the coefficient of that term is 1, you don't explicitly include it.
Wouldn't it be more consistent to include the 1 in your formula in the first place?

Unless I've missed it, the formulas suggested so far (including mine) don't account for an equation like

x² + 3x + 3
or
- x² +3x + 3

If you have the possibility of such equations, would the second one above still have a space between the - sign and the x2 or would it appear as
-x² + 3x + 3

 

[StephenCrump]

D2: =IF(INT(SQRT(C2))=SQRT(C2),(-A2+SQRT(C2))/B2,"("& A2 & "+ SQRT(" & C2 &"))/"&B2)
E2: =IF(INT(SQRT(C2))=SQRT(C2),(-A2-SQRT(C2))/B2,"("& A2 & "- SQRT(" & C2 &"))/"&B2)

Oops, scrub these minus signs.

 

[Peter_SSs]

I guess I should have read your formula more closely Sorry.

I would point out, however, that if the coeficient is 0, the OP said he wants to display it (see Message #6).

Using your CHAR(178) trick to put the exponent into the equation, the following formula should take the three value in Columns A, B and C and create an equation in the format the OP has indicated he wants...

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

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.