How to automatically extract data?

If I have a column(Data 1) with multiple select (Data: 1,2,3,4,5), when the user has selected numbers, how to automatically extract unused data in column(Data 2) like on screen?
00

This was a fun one

SUBSTITUTE(
  TRIM(CONCATENATE(
    IF(FIND("1", {Data 1}), "", "1 "),
    IF(FIND("2", {Data 1}), "", "2 "),
    IF(FIND("3", {Data 1}), "", "3 "),
    IF(FIND("4", {Data 1}), "", "4 "),
    IF(FIND("5", {Data 1}), "", "5 ")
    )), 
  " ", ", "
)

Strategy: Use a combination IFs and FINDs to insert the number followed by a space if its not found in the first field. Then use TRIM to remove the space at the very end of the resulting string. Then use SUBSTITUTE to replace all spaces with a comma and space.

2 Likes

Thanks for your help,but Numbers is just example, what if I have text, many positions? I must write long formula with each one? Maybe have decision when system itself automatically fills in the data from the column?

Numbers v Text is in this case irrelevant. It also doesn’t matter what positions your select options are in.

If your options include spaces inside them, like “This is an option”, then adjust my methodology to use a trailing unique character other than a space. So

IF(FIND("1", {Data 1}), "", "1 ")

would be something like

IF(FIND("This is an option", {Data 1}), "", "This is an option|")

And the substitute would of course replace any | instead of spaces. Because TRIM only works on spaces, you’re going to end up with a comma at the end.

That’s what the formula field does. I already wrote it for you, all you have to do is replace each number with what your actually potential values are and adjust as described above. Unless {Data 1} has 100 options this should take at most 3 minutes to copy and paste. A formula applies to the entire column, so you only have to do this once.

2 Likes