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?
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?
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!
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.
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
)
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.
this was amazingly helpful THANK YOU @kuovonne - exactly what I was looking for and needed. worked great!
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
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
Not sure although it might have to do with emoji actually being composed of multiple characters.
However, I still think a scripting option is now better than a formula.
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} & ""
This "concatenating" hack wont work if there are multiple matches in the lookup field.
See what happens below:
As you see, the IDs are combined. I need them to remain split either by comma or anything else unique so that I can separate ID's into different fields as you outline above.
Do you have any possible suggestions here?
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).
Have you found a way to deal with this yet? I just run into this and I could not figure out what the issue was until I found your comment.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.