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.
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).
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.
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.
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’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} & ""
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.
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.
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.
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! :star_struck: :partying_face: 
It happened when I was 
last night.
Thank you for letting me know. I didn’t see an announcement.
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.
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. :winking_face:
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 this @kuovonne, just what I was looking for. I do wish there was a cleaner function to do this though!
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.
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!
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.
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!
Yeah - unfortunately, it’s a formula function that remains elusive in Airtable called Split(). I feel your pain.
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.
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.
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.
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 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.
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.
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.
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.
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.
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.
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.
@kuovonne would you mind to take a look?
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.
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.
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?
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?
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.
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?
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.