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?
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}:
CONCATENATE functionsLEN 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.
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).
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.
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} & ""
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.
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.
Woo hoo! 
It happened when I was 
last night.
Thank you for letting me know. I didn’t see an announcement.
On the other hand,
SPLIT function,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?
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. 
Thanks for this @kuovonne, just what I was looking for. I do wish there was a cleaner function to do this though!
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.
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.