# How to return values from one cell to multiple cells, each with diffrent length

I dont now where to begin so...

ex.

``````<*cell* M8> 2x^2-10x+8=0

What ive done so far in the cells: I4,J4,K4;

<*cell* I4>=LEFT(M8;1) returns the value 2

<*cell* J4>=MID(M8;5;3) returns the value -10

<*cell* K4>=MID(M8;10;1) returns the value 8
``````

and so far, its correct! (thats the answers i look for)

But i want a more dynamic function:

ex.2

"But! if the cells M8 text, value, would be" -15x^2+101x-18=0 the left, mid-functions would not deliver the answers i would look for.

Im looking for an function that can make all of this true (bad joke)

• search"from left"stop before x^n return the value(-s) to designated cell (ex. I4)
• search after x^n take all the values before x return the value(-s) to designated cell (ex. J4)
• search after x take all the values before = return the value(-s) to designated cell (ex. K4)

(Sorry guys but im a noob so this is the best i could come up with!) Of Course...i know there is probably a ton of better solutions. Feel free to give them! I just dont now how to start or with function to begin with...

-
Are you trying to write an Excel function or is that just a text string? For the first, I can say you're doing it wrong. For the second, maybe a series of find functions can help (assuming the operations aren't changed). I ask which one is it. – Doktoro Reichard Sep 20 '13 at 21:36
It´s a text string. The three cells I4,J4,K4 are the cells with the functions that will deliver the "specifed" string text. in this case -15x^2+101x-18=0 would be I4=-15,J4=101,K4=18 (so sorry is realy new at this) – The rabbit hole Sep 20 '13 at 21:39

Assuming you have a string that resembles the following:

a x^2 + b x + c = 0

Where a, b and c can be whatever real (in the mathematical sense) numbers (e.g. a quadratic function) then you can use, coupled with your reasoning, FIND functions.

For the first coefficient (a), since it's the first one, you can use the following:

``````=LEFT(M8;FIND("x^2";M8)-1)
``````

Assuming x^2 is unique, then this function will get all characters left of the x^2 term.

The remaining terms follow a similar reasoning. I'm posting this now to enlighten you, I'll write the remaining solutions shortly after.

For the second coefficient (b) you hit a wall, because you need to find "x" two times, since in "x^2" is an "x". There might be better ways of doing this, but it is reasonably clear to write nested FINDs.

``````=MID(M8;FIND("x^2";M8)+3;FIND("x";B2; FIND("x";B2) + 1) - (FIND("x^2";B2) + 3) )
``````

Explaining the reasoning. The coefficient begins right after "x^2" is written. So, first find the first character corresponding to "x^2" and then advance the number of chars that string is (3). Afterwards, you need to know how much chars to take, and that is the difference between the second "x" and the end of the "x^2". The `+1` is needed on the 3rd `FIND` to tell Excel not to look the first one.

The third coefficient (c) bases on the double `FIND` I used earlier.

``````=MID(M8;FIND("x";B2; FIND("x";B2) + 1) + 1; LEN(M8) - FIND("x";B2; FIND("x";B2) + 1) - 2)
``````

`LEN` calculates the total amount of characters in the string. The difference is the number of chars in the last coefficient. The `-2` exists to exclude the `=0`

The only problem with this approach is that positive numbers would have the + mark. You can take them using IF's but it is more cumbersome and, for math purposes, it is an accurate representation.

-
This is realy helpful! Alot of gratitude! But can u make a more dynamic function with a logic function something like this =LEFT(M8;FIND("x^2";M8)-1;IF(FIND<>("x^2");Then(Vlookup("table with correct factor"))) its realy anoying when u are green like me and cant explain with the terms experienced user can – The rabbit hole Sep 20 '13 at 22:17
Can you define "more dynamic"? This is dynamic in the sense no matter what the string is or where it is, it will give you the correct results. – Doktoro Reichard Sep 20 '13 at 22:20
Wait.. are you trying to generalize this (for an infinite number of factors)? If so, this approach may become way too cumbersome, unless you would be able to accept a compromise and write "x^1" – Doktoro Reichard Sep 20 '13 at 22:26
Sorry, what i thought of is this, a function no matterwhat the variabel is (x^3; x^4...) reads it and return the values asked for. I thoguht of making a table with all the variabels(to a limit) and make a look up fuction for it. But thats just to bad way to doing it. So i thougt of making the textstring into binary numbers and extract the right data...i dont now how to do that but that could probably work – The rabbit hole Sep 20 '13 at 22:46
Yeah exactly that... – The rabbit hole Sep 20 '13 at 22:47

This is a development on the previous answer, for n degree equations. But for this to be a simple solution some strict rules have to be done in the formulation of the problem:

• The function needs to have all variables clearly and uniquely defined.

• `x^1` , `x^2`, ... , `x^9` are all valid and all terms before `x^9` need to exist (even if they don't, just say `0 x^8` for instance).
• `x^1` and `x^10` is not valid. Because the second contains the first, the FIND function will be confused. The correct usage would be `x^01` and `x^10`.
• `x^0` doesn't need to exist (and in the last case presented it can't exist).
• Reciprocally, you need to have all variables in the lookup table. The same rules apply.

This said, I now present my data setup:

It shows some things about what is expected from the Function. Besides the usage rules presented above, the coefficients can be written freely. Due to that freedom, however, things might appear to be quite messy. My suggestion is to keep consistence (i.e. `+105.25x^15` or `-205.987x^05`, on all coefficients).

Now, the actual lookup functions:

1. For `x^8`: the function is in essence equal to the one I presented before so I've adapted this to suit my example:

``````=LEFT(\$C\$2;FIND(B4;\$C\$2)-1)
``````

Note the dollar signs on the function containing cell. Although this is a singular function, this will be of help later on.

2. For `x^7` to `x^1`:

The function here would be a lot trickier (nigh impossible) without the initial considerations.

``````=MED(\$C\$2;FIND(B4;\$C\$2) + LEN(B4);FIND(B5;\$C\$2) - (FIND(B4;\$C\$2) + LEN(B4)) )
``````

The logic behind this is to pick up the text that's between terms in sequence. The second argument places the first character after the first term (`x^8`) and the third argument calculates, through difference, the length of the coefficient.

You can thereafter drag down this function all the way up to `x^1`. This is where the dollar signs (aka "locking the cell") comes in handy.

If you don't write the null terms, this function will give errors even on terms that exist.

3. For `x^0`:

``````=MED(\$C\$2;FIND(B11;\$C\$2) + LEN(B11);LEN(\$C\$2) - (FIND(B11;\$C\$2) + LEN(B11)) - 2)
``````

The reasoning is similar to the one I used on the earlier answer.

I have been using quite often the `LEN` function to calculate the size (in chars) of the factors. Although in my example, they are all composed of 3 chars, this way I avoid writing magic numbers in every function and it allows expandability (that is needed for n degree equations).

-
Truly great! Ty for awesome help! Have a realy nice weekend – The rabbit hole Sep 21 '13 at 10:42
If any of these answers helped you, feel free to upvote them and / or to accept one of them (by pressing the check mark below the vote number). – Doktoro Reichard Sep 21 '13 at 11:17