Re: Separate a Look Up field (separated by commas) into separate cells

Vincent_Weselak · ‎Feb 26, 2020

I would like to separate a look up field (values separated by commas) into separate cells.

I have information from Table 1 linked to Table 2 (Junction Table) that is linked to Table 3.

Table 3 has a look up field (puling information form Table 1 to Table 2 into Table 3). The values in the look up field are separated by commas. (I am having a problems applying formulas to the look up field).

Is it possible to separate these values into separate fields?

kuovonne · ‎Feb 26, 2020

You can do this using formula fields. Unfortunately the formulas are rather complex.

Here is the formula for the field {Item1}.

IF( FIND(",", {List}),
  LEFT({List},
    FIND(",", {List})-1
  ),
  {List}
)

Here is the formula for the field {Item2}:

LEFT(
  RIGHT({List},
    LEN({List}) - LEN({Item1}) - 2
  ),
  FIND( ",", 
        RIGHT({List},
          LEN({List}) - LEN({Item1}) - 2
        ) & ","
      )-1
)

Here is the formula for the field {Item3}:

LEFT(
  RIGHT({List},
    LEN({List}) - LEN(CONCATENATE({Item1},{Item2})) -4
  ),
  FIND( ",", 
        RIGHT({List},
          LEN({List}) - LEN(CONCATENATE({Item1},{Item2})) -4
        ) & ","
      )-1
)

Here is the formula for the field {Item4}:

LEFT(
  RIGHT({List},
    LEN({List}) - LEN(CONCATENATE({Item1},{Item2},{Item3})) -6
  ),
  FIND( ",", 
        RIGHT({List},
          LEN({List}) - LEN(CONCATENATE({Item1},{Item2},{Item3})) -6
        ) & ","
      )-1
)

If you need more items, continue the pattern used for {Item3} and {Item4}:

add the next item to both of the CONCATENATE functions
subtract two more from the results of the LEN functions (e.g., for the 5th item, subtract 8; for the 6th item subtract 10, etc.)

By the way, I would like to thank @W_Vann_Hall for this post which provided inspiration for this solution.

See Solution in Thread

kuovonne · ‎Feb 26, 2020

You can do this using formula fields. Unfortunately the formulas are rather complex.

Here is the formula for the field {Item1}.

IF( FIND(",", {List}),
  LEFT({List},
    FIND(",", {List})-1
  ),
  {List}
)

Here is the formula for the field {Item2}:

LEFT(
  RIGHT({List},
    LEN({List}) - LEN({Item1}) - 2
  ),
  FIND( ",", 
        RIGHT({List},
          LEN({List}) - LEN({Item1}) - 2
        ) & ","
      )-1
)

Here is the formula for the field {Item3}:

LEFT(
  RIGHT({List},
    LEN({List}) - LEN(CONCATENATE({Item1},{Item2})) -4
  ),
  FIND( ",", 
        RIGHT({List},
          LEN({List}) - LEN(CONCATENATE({Item1},{Item2})) -4
        ) & ","
      )-1
)

Here is the formula for the field {Item4}:

LEFT(
  RIGHT({List},
    LEN({List}) - LEN(CONCATENATE({Item1},{Item2},{Item3})) -6
  ),
  FIND( ",", 
        RIGHT({List},
          LEN({List}) - LEN(CONCATENATE({Item1},{Item2},{Item3})) -6
        ) & ","
      )-1
)

If you need more items, continue the pattern used for {Item3} and {Item4}:

add the next item to both of the CONCATENATE functions
subtract two more from the results of the LEN functions (e.g., for the 5th item, subtract 8; for the 6th item subtract 10, etc.)

By the way, I would like to thank @W_Vann_Hall for this post which provided inspiration for this solution.

Vincent_Weselak · ‎Feb 27, 2020

Thanks for the reply. I found a similar formula used for excel that worked utilizing SUBSTITUTION function and MID function.

MID({Field1},FIND(“CHAR(1)”,SUBSTITUTE({Field 1},",",“CHAR(1)”,1))+2,FIND(“CHAR(2)”,SUBSTITUTE(Field 1},",",“CHAR(2)”,2))-2-FIND(“CHAR(1)”,SUBSTITUTE({Field 1},",",“CHAR(1)”,1)))

Where “CHAR(1)” replaces first comma and “CHAR(2)” replaces second comma.

One thing I found is that I can not apply this formula to a lookup field. When new entries come in, I have to manually paste the text from the lookup field into “single line text” field. Then the formula will work on this type of field.

Was wondering if there was some crafty way to break apart the lookup field without having to copy and paste when new entries come in? Maybe using some functionality of the lookup field (unknown to me).

Bill_French · ‎Feb 27, 2020

And it doesn’t scale.

The new Script Block was released today to all Pro users, so you may want to look at that as a way to create a smart and scalable approach to parsing any type of combined string values (example).

Answer: Create a new formula field that captures the value of the lookup field and use that for parsing instead of the lookup field.

Alternate: Create a script block that extracts the lookup value and parses using the far more advanced Split() method.

kuovonne · ‎Feb 27, 2020

Bill French’s answer of creating a formula to cast the lookup field as a string is the answer. There is not a function that explicitly casts a field as a string, but it can be accomplished with concatenating the field with an empty string:

{List} & ""

kuovonne · ‎Feb 27, 2020

Quite true. I agree 100%.

But with a slightly simpler formula, hopefully it will be easier for users to extrapolate the formulas for Item5, Item6, etc.

Plus, we don’t know how Airtable limits the scale of chained formulas. If the limit is based on total computing time, a more optimized formula will require less computing power and might be able to eek out a little more iterations. If the limit is on the number of chained formula fields, having one less formula field in the chain would grant the ability to have one more item field.

I also agree that the Script Block solution would be far more robust, even though I haven’t seen it, as it is not yet publicly available.

On the other hand, some users may want this functionality with formulas, either because they do not have a Pro subscription yet, or because they are intimidated by the idea of a Script Block.

Bill_French · ‎Feb 27, 2020

I wasn’t clear in my scalability comment - it doesn’t scale humanistically. The approach is wholly challenged by the user’s ability to craft complex formulas which often lead to sh*t-shows like this.

Script Block (javascript) is no more humanistic than these crappy formulaic approaches, but at least it scales through modern array handling.

The correct answer is neither formulas OR script blocks; rather - just add a Split() functionality to ALL string handling.

Publically released 10 hours ago.

kuovonne · ‎Feb 27, 2020

Woo hoo! :star_struck: :partying_face: :boom: :dizzy:

It happened when I was :zzz: :zzz: :zzz: last night.

Thank you for letting me know. I didn’t see an announcement.

kuovonne · ‎Feb 27, 2020

On the other hand,

in the specific case for this topic (Separate a string),
in the absence of a SPLIT function,
for those who don’t have a Pro subscription,

my refactored formulas do scale humanistically better than the other versions. The main caveat is to do the editing outside of Airtable. Even the very simple Notepad that comes standard with Windows (vs. the code editor NotePad++) works well enough for this level of editing.

At least the formula fields are well documented, making these complex formulas possible at all. One of the reasons that I chose Airtable over other cloud-based databases was the robustness of the formulas and the clarity of their documentation. (An undocumented function is useless; a poorly documented function is frustrating.)

For the Script Block, JavaScript supports comments, making it more human readable. And there are plenty of JavaScript developers and resources for learning JavaScript.

I would love a Split formula function.

You wouldn’t happen to have the inside scoop on any of Airtable plans, would you?

Bill_French · ‎Feb 27, 2020

Yes, this is true, but unfortunately it still requires some very concerted abstract formula-hacking and most users - even developers - get sideways attempting these insane vertical climbs of Mt Improbable.

No - I’m the least-informed person to ask this. Airtable is very careful about giving old dudes, especially me, the insider track because I complain far too much. :winking_face: