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

[Vincent_Weselak]

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?

[Mitchell_Wells1]

Thanks for this @kuovonne, just what I was looking for. I do wish there was a cleaner function to do this though!

[Daniel]

Hi @kuovonne, thanks for posting this. I am almost there, but in my example, the values are numbers instead of strings. So while your formula works, I cannot treat the resulting split out values in the columns as numbers for further calculations. I tried wrapping your formulas in the VALUE() formula, which worked for {Item1} but then broke the formulas for {Item2} and further.

Do you know of a way to separate a field of comma separated numbers into resulting number type fields, so that I can make use of those values as numbers instead of text?

Thanks!

[kuovonne]

You could try coercing the number back into strings by using

{Item1} & ""

However, at best it would work only for integers from 1-999, and the formula would be even more difficult to maintain.

That formula was written before Scripting block was available, and I vastly prefer Scripting block for situations like this.

[Bill_French]

Daniel:

Do you know of a way to separate a field of comma separated numbers into resulting number type fields, so that I can make use of those values as numbers instead of text?

Yeah - unfortunately, it’s a formula function that remains elusive in Airtable called Split(). I feel your pain.

[Daniel]

Thanks! For my use, it doesn’t seem like the scripting block will work, because it has to be run manually, where-as ideally my use case requires the field to be split as the user inputs the data without requiring any user action. Thanks for getting back to me so quickly.

[kuovonne]

You might be interested in the scripting action beta which could cause the script to be run automatically.

[Bill_French]

Daniel:

my use case requires the field to be split as the user inputs the data without requiring any user action.

@kuovonne is correct; it absolutely will work with the new triggered action capability. I got the impression that writing javascript ruled out the use of script block and thus, a formula field was mandatory.

[kuovonne]

Daniel:

my use case requires the field to be split as the user inputs the data

Could you have the user input the data in separate fields instead of a typing s comma separated list of numbers? It is far easier to join values into a single string than to split out values. If your user is entering data into a form you can use conditional fields to show the proper number of inputs.

If your user is inputting the data in Airtable itself, you could also have s button field that launches a script. After entering the data, the user clicks the button to run the script. It isn’t completely automatic, but it doesn’t add much interaction.

[N_Bigglesworth]

This discussion is useful, thank you! To further complicate things:

Is there a way of splitting out a field of, for example, 82 comma-separated image URLs into fields divisible by 25 comma-separated image URLs?

Ie. in my example attached screenshot, let’s say the leftmost column (“Images: All, As URLs”) has 82 comma-separated image URLs (which represent 82 images uploaded via an Airtable form into a single field):

I would like the next column (“25 Images: A”) to have comma-separated URL Image 1 through Image 25.

The column to the right (“25 Images: B”) would hold Image 26 through Image 50.

Column to right of that (“25 Images: C”) would hold image 51 through Image 75.

Rightmost column (“25 Images: D”) would hold Image 76 through Image 82.

FYI: my use case is that I’d be doing this to accommodate Webflow’s limitation of 25 images per multi-image CMS field. But don’t worry about Webflow, in terms of solving this problem.

[kuovonne]

Given the early discussion in this thread about problems with scale, future readers of this thread may be interested in this related thread documenting problems scaling up past 98.