Skip to main content

Hi all,

I have a lookup field that lists values for the same person (e.g. 0,0,1,0,1,0,0,0). I want to pull out the non-zero values into columns by themselves. Some unique aspects are that:



  • the number of values varies by person/case

  • the position of the non-zero values varies by person/case

  • some people/cases will have only 1 non-zero value, others will have 2 or 3


The data table looks something like this:

|name|score|

|—|---|

|Amanda|0,0,0,0,1,0,0|

|Jeffrey||

|Joanna|1,0,0,0|

|Julianna|0,0,1,0,0|

|Kelsie|1,0,0,0,|

|Leandro|0,0,0,1,0,1|

|Victoria|1,0,0,0,0,1,0,0,0|


what’s I’d like is:

|name|score 1|score 2|

|Amanda|1||

|Jeffrey|||

|Joanna|1||

|Julianna|1||

|Kelsie|1||

|Leandro|1|1|

|Victoria|1|1|


Thank you!

Jenny

Welcome to the community, Jenny! :grinning_face_with_big_eyes: Here’s how I’d approach this problem. Some of these first formulas could be combined, but I’m listing them separately for clarity.


First you need to get rid of the separating commas. Thankfully these are only added for display by Airtable; they’re not really part of the data. However, it’s still a step that needs to be done, and it’s done like this (using a field name of {Score} for your lookup field):


Score & ""

This tells Airtable to concatenate all of those individual looked-up items into a single string, with no separators, which ends up looking like this:



Next you can remove the zeroes with a SUBSTITUTE() function:


SUBSTITUTE({Mashed Scores}, "0", "")


Again, these steps were split out to describe their individual purposes, but they can be combined into a single field with this formula:


SUBSTITUTE(Score & "", "0", "")

Now you start adding individual score fields. For {Score 1} you just need to see if there’s a score at all. If so, grab the leftmost value:


IF({No Zeroes}, LEFT({No Zeroes}, 1))

For {Score 2}, check for a length greater than one. If so, take only the second character:


IF(LEN({No Zeroes}) > 1, MID({No Zeroes}, 2, 1))

Similar for {Score 3}, increasing some of the formula values by one:


IF(LEN({No Zeroes}) > 2, MID({No Zeroes}, 3, 1))

Repeat for as many score fields as you need.


Reply


Terms & Conditions