Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

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

Topic Labels: Formulas
Solved
Jump to Solution
22470 35
cancel
Showing results for 
Search instead for 
Did you mean: 
Vincent_Weselak
4 - Data Explorer
4 - Data Explorer

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?

35 Replies 35

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

Daniel
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

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.

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

Daniel
5 - Automation Enthusiast
5 - Automation Enthusiast

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.

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

@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.

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
6 - Interface Innovator
6 - Interface Innovator

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):
Screen Shot 2021-05-27 at 2.10.38 PM 2

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.

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.