Get comma separated values from a cell and copy the values into independent cells

Nokwanda

I have a column: "Materials QTY" values in this cell: "10,5,100,1400,11000,2" (comma separated no spaces).

I have independent columns ie. "Material 1 QTY", "Material 2 QTY", "Material 3 QTY" up to 20.

I need to extract the 1st value from the "Materials QTY" and populate it in "Material 1 QTY"; the 2nd value from the "Materials QTY" and populate it in "Material 2 QTY" and do the same for the rest of the values.

Is this possible?

James Harris

For something like this, it would be really helpful if the FIND() function gave the ability to find the Nth instance of a string. e.g. the 5th comma in a string. However, the SUBSTITUTE() function does provide this distinction, so we can use FIND() and SUBSTITUTE() in tandem to do what we need.

So, for example, if you substitute the 5th comma for a different character, and then find that character, we can determine where the 5th comma is in the original string.

The result of all this is that we can do what you need.

Here are the formulae I've used for the above screenshot.

Material 1 QTY: =IF(FIND(",", [Materials QTY]@row) = 0, [Materials QTY]@row, MID($[Materials QTY]@row, 1, FIND(",", $[Materials QTY]@row, FIND("*", SUBSTITUTE($[Materials QTY]@row, ",", "*", 1)) - 1) - 1))

Material 2 QTY: =IF(SUBSTITUTE($[Materials QTY]@row, ",", "*", 1) = $[Materials QTY]@row, "", IF(SUBSTITUTE($[Materials QTY]@row, ",", "*", 2) = $[Materials QTY]@row, RIGHT($[Materials QTY]@row, LEN($[Materials QTY]@row) - FIND("*", SUBSTITUTE($[Materials QTY]@row, ",", "*", 1))), MID($[Materials QTY]@row, FIND("*", SUBSTITUTE($[Materials QTY]@row, ",", "*", 1)) + 1, FIND(",", $[Materials QTY]@row, FIND("*", SUBSTITUTE($[Materials QTY]@row, ",", "*", 2)) - 1) - FIND(",", $[Materials QTY]@row, FIND("*", SUBSTITUTE($[Materials QTY]@row, ",", "*", 1))) - 1)))

And generally...

Material N QTY: =IF(SUBSTITUTE($[Materials QTY]@row, ",", "*", N-1) = $[Materials QTY]@row, "", IF(SUBSTITUTE($[Materials QTY]@row, ",", "*", N) = $[Materials QTY]@row, RIGHT($[Materials QTY]@row, LEN($[Materials QTY]@row) - FIND("*", SUBSTITUTE($[Materials QTY]@row, ",", "*", N-1))), MID($[Materials QTY]@row, FIND("*", SUBSTITUTE($[Materials QTY]@row, ",", "*", N-1)) + 1, FIND(",", $[Materials QTY]@row, FIND("*", SUBSTITUTE($[Materials QTY]@row, ",", "*", N)) - 1) - FIND(",", $[Materials QTY]@row, FIND("*", SUBSTITUTE($[Materials QTY]@row, ",", "*", N-1))) - 1)))

So the 1st formula is specific, and the rest, from 2 through to however many columns you need, follows the general formula above. Unfortunately, I don't think Smartsheet will let you drag-fill this across, so I suspect the formulae will need to be amended individually.

Let me know if that works for you.

Thanks,

James