Extract text of varying length from middle of an equation

P

phmalu

Board Regular
Joined
Jun 21, 2017
Messages
52
Office Version
  1. 2019
Platform
  1. Windows
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>
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
With your equations (as shown in Message #1) in Column A starting at Row 1, put this formula in cell B1 and copy it across to Column D, then copy those three formulas down to the bottom of your data...
Code: 
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"x²",""),"x","")," ",REPT(" ",100)),200*COLUMNS($B:B),100))
 
Last edited:
Upvote 0
Rick Rothstein said:
With your equations (as shown in Message #1) in Column A starting at Row 1, put this formula in cell B1 and copy it across to Column D, then copy those three formulas down to the bottom of your data...
Code: 
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"x²",""),"x","")," ",REPT(" ",100)),200*COLUMNS($B:B),100))
Click to expand...
WOW, it works really well
However, negative B's are shown as positive. All the rest is 100% tho.
Thank you!

ps: I known it's my own fault, first equation should be:

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

<tbody>
</tbody>
 
Upvote 0
phmalu said:
WOW, it works really well
However, negative B's are shown as positive. All the rest is 100% tho.
Click to expand...
Sorry, my fault, try this formula instead...
Code: 
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"- ","+ -"),"x²",""),"x","")," ",REPT(" ",100)),200*COLUMNS($B:B),100))
 
Upvote 0
Rick Rothstein said:
Sorry, my fault, try this formula instead...
Code: 
=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"- ","+ -"),"x²",""),"x","")," ",REPT(" ",100)),200*COLUMNS($B:B),100))
Click to expand...
Now it's working flawlessly, thank you so much.
I wish I could come up with complicated formulas like this one on my own, you helped me immensely.
 
Upvote 0
Hi,

What if one or both of the coefficients for x or x² is unity?

For example, if you had:

y = -x² + x - 1

I presume you'd want the results to be -1, 1 and -1 (which Rick's formula will not provide)?

Or is unity always explicit in your equations, i.e. the above would be written (rather unconventionally):

y = -1x² + 1x - 1

Regards
 
Upvote 0
XOR LX said:
What if one or both of the coefficients for x or x² is unity?

For example, if you had:

y = -x² + x - 1

I presume you'd want the results to be -1, 1 and -1 (which Rick's formula will not provide)?
Click to expand...
Good point!!! I can't believe I did not think of that. :oops:

This formula should fix the problem...

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"-x","-1x")," x"," 1x"),"- ","+ -"),"x²",""),"x","")," ",REPT(" ",100)),200*COLUMNS($B:B),100))
 
Last edited:
Upvote 0
Rick Rothstein said:
Good point!!! I can't believe I did not think of that. :oops:

This formula should fix the problem...

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A1,"-x","-1x")," x"," 1x"),"- ","+ -"),"x²",""),"x","")," ",REPT(" ",100)),200*COLUMNS($B:B),100))
Click to expand...

Those are regression equations that represent the results from a field experiment. That means the coefficients are highly unlikely to be unity. Hence, they are always fractional so I believe the new formula wasn't necessary.
However, I will apply the changes just in case.
Thank you both once again!
 
Upvote 0
XOR LX said:
Hi,

What if one or both of the coefficients for x or x² is unity?

For example, if you had:

y = -x² + x - 1

I presume you'd want the results to be -1, 1 and -1 (which Rick's formula will not provide)?

Or is unity always explicit in your equations, i.e. the above would be written (rather unconventionally):

y = -1x² + 1x - 1

Regards
Click to expand...
And what if I wanted to solve this using the =MID + FIND command? Is it possible to solve it that way? In this case we could set 3 different formulas, one for A, B and C.

That is because 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. This generic rule never changes as far as I can tell.

On my own I was able to come up with the formula "=MID(C3;5;FIND("x";C3;5)-5)" for A (and it seems to work every time), but I was unable to adapt it so I could find B and C also using the same logic. Thank you once again.
 
Upvote 0
phmalu said:
And what if I wanted to solve this using the =MID + FIND command? Is it possible to solve it that way? In this case we could set 3 different formulas, one for A, B and C.
Click to expand...
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Extracting text within varying length phrases
Replies
8
Views
869
Peter_SSs
Peter_SSs
Z
  • Question
Extract 1-N value(s) from a string of text in 1 cell - multiple extraction values in separate tables (excel formula needed)
Replies
2
Views
913
zen
Z
A
  • Question
Extracting Text of a variable length from the middle of a string
Replies
2
Views
2K
Anthea
A
M
  • Question
VBA to extraxt dates and times from text string and calculate differences
Replies
3
Views
284
magges
M
M
  • Question
Extract Characters from String
Replies
4
Views
737
AlKey
A

Forum statistics

Threads
1,214,649
Messages
6,120,730
Members
448,987
Latest member
marion_davis

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top