0

Shown here Shown above is a formula, where, in the highlighted parts, the data values are entered manually as an array. My goal is to implement the values in column C (entered in one cell followed by a semi-colon) automatically in this formula without the need to input them manually. Values in column C need to be in one cell.

My question is whether there is a certain way to define data values only in one cell and then implement it as an array in a formula?

Or would it be possible to simplify the formula, so the highlighted array is only entered once in a formula?

Aregakia
  • 13
  • 5
  • Use FILTERXML: https://stackoverflow.com/questions/61837696/excel-extract-substrings-from-string-using-filterxml – Scott Craner May 06 '21 at 15:48
  • One more recommendation, Look into the `LET()` function to simplify the formula. – Scott Craner May 06 '21 at 15:49
  • FILTERXML doesn't work for Excel for Mac nor Excel Online. However, I need this solution to be applicable to all Excel versions if it is possible. – Aregakia May 06 '21 at 18:37
  • without filterxml: `--TRIM(MID(SUBSTITUTE(C2,";",REPT(" ",999)),(ROW($ZZ1:INDEX($ZZ:$ZZ,LEN(C2)-LEN(SUBSTITUTE(C2,";",""))+1))-1)*999+1,999))` will return the array. Put that in everywhere you have `{0.5;7;9;5}` – Scott Craner May 06 '21 at 20:52
  • It seems like this solution makes the formula spill. However, what is needed here is to get the result value in a single cell. – Aregakia May 06 '21 at 21:12
  • I thought you wanted to take a single cell C2 and use that as an array. IF you put that in a formula ie: `=COUNT(--TRIM(MID(SUBSTITUTE(C2,";",REPT(" ",999)),(ROW($ZZ1:INDEX($ZZ:$ZZ,LEN(C2)-LEN(SUBSTITUTE(C2,";",""))+1))-1)*999+1,999)))` it will not spill. It will return `4` it is to be used in place of the hard coded `{0.5;7;9;5}` in your formula. – Scott Craner May 06 '21 at 21:15
  • Thank you! It worked. Would you be able to explain the reasoning behind the formula? – Aregakia May 06 '21 at 21:44
  • After implementing this formula for one row and dragging it to apply for the next, it didn't work. I only need the "C2" value to change and for others to be fixed. – Aregakia May 06 '21 at 21:58
  • Change the `$ZZ1` to `$ZZ$1` – Scott Craner May 06 '21 at 21:59
  • The formula basically makes a very long string with 999 spaces in between the values. This allows us to use an array of starting points, 1,1000,2000,3000 and make them 999 characters long which will include each number and a lot of spaces both before and after. The trim removes the extra spaces and the `--` turns it into numbers. – Scott Craner May 06 '21 at 22:01
  • Thank you! What does ZZ refer to in this case? – Aregakia May 07 '21 at 17:41
  • column `ZZ`. It is just a counter, it does not matter what column is chosen. I like to use that so the user knows it does not refer to a specific data set. – Scott Craner May 07 '21 at 17:43
  • What specific purpose does it serve in this specific formula? – Aregakia May 07 '21 at 18:21
  • `$ZZ$1:INDEX($ZZ:$ZZ,LEN(C2)-LEN(SUBSTITUTE(C2,";",""))+1)` creates a range that starts in ZZ1 and ends in ZZ on the row of the number of items in the cell, in this case 4. Then `ROW(...)` creates an array of row numbers `{1,2,3,...}` to the number of items in the cell. Then the `-1` shifts that to `{0,1,2,...}` It is that that is multiplied to the `999` – Scott Craner May 07 '21 at 18:52

0 Answers0