Re: Formula to Separate Concatenated Items?
0
3344
6
Well, it can be done, but it will probably be ugly! :winking_face:
I’d first create a field called something like {URLCount} with the following formula
LEN({URLString})-LEN(SUBSTITUTE({URLString},',',''))+1
(That’s @Simon_Brown’s clever trick to count the number of [whatevers] in a list of [whatevers]. It can be hard to deconstruct, but it takes the length of the original string and subtracts from it the length of the string with all the separation-character commas removed — essentially counting the number of commas — to which it then adds 1 to account for the final element in the list, which lacks a trailing comma.)
After that, I’d define my 20 extracted URL fields, each with a variant of this formula:
So for {URL3} the formula would be
IF(
{URLCount}>=3,
MID(
{URLString}&',',
FIND(
',',
{URLString},
(LEN({URL1})+2)
)+1,
FIND(
',',
{URLString}&',',
(LEN({URL1})+2)+
(LEN({URL2})+2)
)-FIND(
',',
{URLString}&',',
(LEN({URL1})+2)
)-1
)
)
and for {URL4}
IF(
{URLCount}>=4,
MID(
{URLString}&',',
FIND(
',',
{URLString},
(LEN({URL1})+2)+
(LEN({URL2})+2)
)+1,
FIND(
',',
{URLString}&',',
(LEN({URL1})+2)+
(LEN({URL2})+2)+
(LEN({URL3})+2)
)-FIND(
',',
{URLString}&',',
(LEN({URL1})+2)+
(LEN({URL2})+2)
)-1
)
As you can imagine, by the time you get to {URL20}, those chained LEN() statements will be pretty long. (And, yes, it would probably be better practice and more efficient to sum the LEN()s and then add 2 * the number of LEN()'s, but adding 2 to each statement individually is far more copy-and-paste-friendly.)
[BTW, Airtable will let you compose formulas offline — under Windows, I personally recommend Notepad++ for its automatic indentation and parenthesis-matching features — and then copy-and-paste indented code into the formula field. What’s more, if you copy-and-paste the formula from Airtable back into Notepad++ to correct or modify, the indentation is maintained. Offline composition makes creating behemoths like these far more manageable.]
Obviously, the formulas for {URL1} and {URL2} will be a little different; I’m including them for completeness’ sake.
{URL1}:
IF(
{URLCount}>=1,
MID(
{URLString}&',',
1,
FIND(
',',
{URLString}&',',
1
)-1
)
)
{URL2}:
IF(
{URLCount}>=2,
MID(
{URLString}&',',
FIND(
',',
{URLString},
0
)+1,
FIND(
',',
{URLString}&',',
(LEN({URL1})+2)
)-FIND(
',',
{URLString}&',',
0
)-1
)
)
I’ve put together a little demo of this code (up through {URL4}, only — didn’t want to have all the fun :winking_face: ) here.
Hope this helps!
