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?

Shneor · ‎Oct 28, 2021

I’m trying to implement the solutions from this thread but can’t make it work.

Issue 1

I have two rollup columns with array strings separated by commas. Some of the cells have multi and some none.

I’m trying to combine both rollups columns into a new column and keep it comma-separated and or empty when the column is empty*.

That’s the current formula (I’ve tried many)

ARRAYJOIN({Rollup 1}, ",") & "," & ARRAYJOIN({Rollup 2}, ",")

Issue 2

Then, in another table, I have quite the opposite issue:

I have got a Lookup column looking on multiple (based on another linked records) of the above Combine Rollup* columns and returns a messy string now with duplicates commas and spacing:

What I’m trying to achieve here is the get only the first record id and put it in the next column and the second record id into the following column and so on and so forth.

Highly appreciate any help.

Shneor · ‎Oct 29, 2021

@kuovonne would you mind to take a look?

kuovonne · ‎Oct 29, 2021

Let’s think through this screen capture. Your formula works fine when there are values in both {Rollup 1} and {Rollup 2}. If there are no values, you get a comma that yo don’t want. If there is a value in only {Rollup 1}, you get a trailing comma that you don’t want. If there is a value in only {Rollup 2}, you get a leading comma that you don’t want. So you want to show that comma only when you have values for both rollups.

CONCATENATE(
  {Rollup 1},
  IF( 
    AND({Rollup 1}, {Rollup 2}),
    ","
  ),
  {Rollup 2}
)

Try this and see if it clears up the commas for your second issue. You may also want to use a rollup instead of a lookup field. As for removing duplicates–you cannot do that with formulas.

Harrison_Harry_ · ‎Nov 18, 2021

Hi @kuovonne,

Thanks so much for providing this. I am pulling information from a shipstation account using zapier and it enters AT as an array, similar to a lookup field.

I’m just trying to wrap my head around your formula because i want to be able to fully understand what’s happening if I am going to replicate this in my base. I haven’t used these text functions before so this is all new to me.

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

So this is saying, if the program finds a comma in the List Column, then starting at the beginning of the string, go until you find a comma - 1, thus getting rid of the comma. If no comma, then just print list. Is that correct?

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
)

This is the one that is confusing to me. I don’t really get how this portion works. It looks like you are having it start at the end of the string, and then going backwards the difference in length between the full string and the item #1. Then starting from the beginning of the string you find the next comma, and somehow end up with just word.

I see it in theory, you are pretty much identifying the beginning and end of the next word using the previous output as a reference point. But the finer points are alluding me.

Would you be able to explain the logic behind this and how it’s working?

kuovonne · ‎Nov 18, 2021

The formula works pretty much as you described. Find the “remainder” of the string after removing earlier items, then look for the comma in the remainder to determine the end of the current item.

However, I crafted this set of formulas over a year and a half ago before Scripting app was released. I no longer recommend using these formulas. Instead, I recommend parsing the string using Scripting, which provides access to the JavaScript split function. If you have a pro workspace, you can use a scripting automation. If you do not have a pro workspace, you can still use scripting–you will just have to run the script manually.

Harrison_Harry_ · ‎Nov 18, 2021

I do have a Pro subscription but I do not know JavaScript at all. Is there an example of this i could see for reference so I can begin to learn?

kuovonne · ‎Nov 18, 2021

If you are interested in learning how to write scripts for Airtable, I suggest you start with an introductory course in JavaScript, and then layer on the Airtable Scripting documentation once you know the basics of JavaScript. When you get stuck, post your attempts and someone will probably help you.

If you are not interested in learning to write scripts, decide if you want to invest your time in searching to see if someone has already written a script that you can use, or if you want to invest the money in hiring someone to write the script for you.

Best of luck!

Matthew_F · ‎Jan 31, 2022

Thank you so much, kuovonne! I adapted this for my use case and copied the formulas below for anyone else who is trying to do this with URLs from a linked record.

I did the following using a rollup field that rolls up URLs from a linked record, and in that rollup field I have “ARRAYJOIN(values)” so there is a comma added by airtable (because it seems when you just use a lookup field, when multiple values display airtable does not technically put a comma in there that other formulas can grab, versus when you use a rollup it does).

This assumes you have up to 5 URL’s in a single URL Rollup field that uses the aggregation formula, "ARRAYJOIN(values)"

Here is the formula for the field {URL 1}.

IF( FIND(",", {URL Rollup}),
  LEFT({URL Rollup},
    FIND(",", {URL Rollup})-1
  ),
  {URL Rollup}
)

Here is the formula for the field {URL 2}.

LEFT(
  RIGHT({URL Rollup},
    LEN({URL Rollup}) - LEN({URL 1}) - 1
  ),
  FIND( ",", 
        RIGHT({URL Rollup},
          LEN({URL Rollup}) - LEN({URL 1}) - 1
        ) & ","
      )-1
)

Here is the formula for the field {URL 3}.

LEFT(
  RIGHT({URL Rollup},
    LEN({URL Rollup}) - LEN(CONCATENATE({URL 1},{URL 2})) -2
  ),
  FIND( ",", 
        RIGHT({URL Rollup},
          LEN({URL Rollup}) - LEN(CONCATENATE({URL 1},{URL 2})) -2
        ) & ","
      )-1
)

Here is the formula for the field {URL 4}.

LEFT(
  RIGHT({URL Rollup},
    LEN({URL Rollup}) - LEN(CONCATENATE({URL 1},{URL 2},{URL 3})) -3
  ),
  FIND( ",", 
        RIGHT({URL Rollup},
          LEN({URL Rollup}) - LEN(CONCATENATE({URL 1},{URL 2},{URL 3})) -3
        ) & ","
      )-1
)

Here is the formula for the field {URL 5}.

LEFT(
  RIGHT({URL Rollup},
    LEN({URL Rollup}) - LEN(CONCATENATE({URL 1},{URL 2},{URL 3},{URL 4})) -4
  ),
  FIND( ",", 
        RIGHT({URL Rollup},
          LEN({URL Rollup}) - LEN(CONCATENATE({URL 1},{URL 2},{URL 3},{URL 4})) -4
        ) & ","
      )-1
)

A_Horn · ‎Feb 04, 2022

this was amazingly helpful THANK YOU @kuovonne - exactly what I was looking for and needed. worked great!

Partnerships_Te · ‎Sep 02, 2022

Thanks for this very helpful thread. The formula works perfectly for everything I need except when separating emojis. Any idea why this might be happening and how to fix it? @kuovonne