Formula to Separate Concatenated Items?

There is a technique for doing this which involves using Javascript Zapier step to convert the string into first an array and, then, an array of objects and returning this from the step.

The clever thing about this is that the Zapier steps following the Javascript Step (returning an array of objects) runs for EACH of the objects in the array.

You may be better looking up a field which holds the IDs of the individual presentation records so that you can then use these to search for the records in a Find step and get the other data from the records that you need for the WebMerge.

There is one other thing you need to watch out for which is that Zapier will only loop through 25 objects returned from a Code Step in this way - but there is a workaround if this is an issue by splitting the original array up before turning it into an array of objects - sounds complicated but it’s not that bad.

I discovered this technique doing some work for a client and could either post some sample code if you have a little Javascript or get in touch directly if you would like some help.

Julian

Thanks so much @Julian_Kirkness. I don’t have any Javascript experience, so this might be too much of a challenge to take on in the timeframe I have, but I’m going to study it a bit more and will message you.

Also, FYI, Matt from Airtable suggested this can be done manually using string functions such as FIND to determine the index of a separator and MID to extract a substring before or after a particular separator.

Hi Arlo

You could definitely split up the values inside Airtable - but I assume you need all the values (an unknown number of them presumably) in Zapier for merging?

The one aspect I haven’t looked at is how this would be sent to WebMerge using my technique - in my use of this so far I have been creating multiple Airtable records (in another table) when certain criteria are met in the triggering table. I haven’t used WebMerge ‘in anger’ yet although I have played a little.

I will contact you directly.

Hi Arlo

I have done a little bit of research into this and I think I have a suggestion. This involves creating a JSON string using formula fields in Airtable containing key/value pairs to populate a table in WebMerge. Essentially, you would create a JSON calculated field in each presentation record and then use lookup / rollup and formula fields to format a JSON string in the presenters record with all the information from their bookings. This can be sent to WebMerge by Zapier along with other information as required. Not that simple but no code is required to do this.

Here is a simple table in WebMerge which shows an example:

and here is the test part of the Zap which shows the format of the JSON which is sent to populate it:

This example relates to producing a ‘statement’ detailing time spent on a project.

There is some documentation on this on the WebMerge site. I believe that sending JSON like this is the only way to get WebMerge to produce a table of results (which I assume you would want rather than a separate document for each presentation?).

Just a follow-up here. I was able to achieve my objective by taking a different approach. Instead of exporting each individual field (start time, end time, presenter, presentation title) to WebMerge, I used a formula to concatenate these fields in a single string, with punctuation to make it “friendly”; then I used a rollup to specify a separator between record-strings (" | " in my case). Then finally I sent it to WebMerge and used “explode” code on the specified separator (" | ") to display each string on its own line; also applied text formatting (bold for headline, italic for note, etc).

It took a lot of testing to get it right, but now it works like a dream. Using Zapier as the intermediary, I’ve set it up so just by typing “manifest” in an Airtable field, I can get a formatted document from WebMerge on our letterhead that contains all the info we need for our presenters: full contact details, hotel accommodations, flight itinerary, and on-stage assignments. This gets saved in a Dropbox folder that my whole team has access to, so everyone can get all this info on a single formatted sheet at a moment’s notice.

When I have more time, I’ll try and post on this in “show and tell.” Definitely my biggest Airtable success story.

BTW, below is the code I used in Webmerge (where “manifests_sched” is the field I’m getting from Airtable that contains the string with " | " separators)

Put this in the Field Map:

{json_encode(explode(";", $manifests_sched))}

And this in your document:

{foreach from=$manifests_sched item=_row}{$_m = explode("|", $_row)}

{$_m.0}

{$_m.1}

{$_m.2}

{/foreach}

Hi Julian,

Thanks for posting this. This is exactly what I need to do, but I can’t seem to manipulate a field to get it to output data in JSON array format. Can you let me know how you did it please?

Hi Albert

Essentially, I just used Concatenate() to build the JSON String:

Because this particular case then involved using a rollup to get all of these values for a particular client / project I then used Substitute to format the multiple values correctly:

I hope this helps!

Julian

This is amazing! Thank you for a quick response.

Hi! I’m trying to something similar. I have a string of URLs that I would like to separate so they are their own column in Airtable.

Ie

Www.test1com, www.test2com, www.test3com

To become three separate columns in Airtable?

Can anyone help? @Julian_Kirkness @Arlo_Haskell

Hi Annie

Can I ask - are there always 3 URL - or does it vary?

Julian

Hi Julian! Thanks for the followup. It might vary. The range is anywhere

from 1 to 20.

Cheers.

Annie

Would you then be looking to populate 20 columns? Would these be newly created columns whenever a new URL was encountered, or would a set of columns — {NewURL01}, {NewURL02}, and so on — possibly contain a range of URLs that vary from record to record?

Hi @W_Vann_Hall - Yes, I would be looking to then populate 1-20 columns where each column is the newly separated item. The intent is then to create 1-20 Airtable views to trigger an email notification.

Suggestions? Thoughts? Apprec the help in advance!

Tagging @Julian_Kirkness for the earlier response from Saturday. Thanks in advance for the help!

Hi Annie

At the moment, I can’t immediately think of a way to achieve this in Airtable although it may be possible with Zapier (or code via the API).

However, I wonder if there’s any other way - are the URLs being populated in a Rollup field for example - in which case could something be done in the underlying records.

Wherever possible I try to work with the database and get it to do as much of the work as possible.

Julian

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!

<weasel words>

I probably should mention I have no idea why this works. Obviously, it’s highly dependent upon the order in which fields are evaluated, and that sort of scheduling issue has been the bane of system and compiler developers ever since computers. Airtable may very well have a wicked smart scheduling routine busily at work behind the scenes, protecting reckless fools like me from the perils of what we don’t know we don’t know, but as I’ve not seen any comments as to that being the case, I should caveat the previous reply by saying you should watch for possible failures of the algorithm or, more likely, for repeated display of the base to return different results. Again, this caution does not reflect any concern about or reluctance to use Airtable but just an acknowledgement that I have done something here that has been known to trip up some platforms or languages.

</weasel words>

You are amzing! Thank you so much for this help @W_Vann_Hall

@W_Vann_Hall

This post and your example base have been BEYOND helpful. But I’m struggling to debug my code for “URLs” 5 and 6… Could you share examples of those formulas or help me find my errors in the below (for “Style Name 5”)?

IF(

    {Comma Count}>=5,

    MID(

        {Style Name}&'\n',

        FIND(

            '\n',

            {Style Name},

            	(LEN({Style Name 1})+2)+

                (LEN({Style Name 2})+2)+

		(LEN({Style Name 3})+2)

            )+1,

        FIND(

            '\n',

            {Style Name}&'\n',

            	(LEN({Style Name 1})+2)+

                (LEN({Style Name 2})+2)+

                (LEN({Style Name 3})+2)+

		(LEN({Style Name 4})+2)

            )-FIND(

                '\n',

                {Style Name}&'\n',

                (LEN({Style Name 1})+2)+

                (LEN({Style Name 2})+2)+

 		(LEN({Style Name 3})+2)

                )-1

            )

IF(

    {Comma Count}>=5,

    MID(

        {Style Name}&'\n',

        FIND(

            '\n',

            {Style Name},

            	(LEN({Style Name 1})+2)+

                (LEN({Style Name 2})+2)+

		(LEN({Style Name 3})+2)

            )+1,

        FIND(

            '\n',

            {Style Name}&'\n',

            	(LEN({Style Name 1})+2)+

                (LEN({Style Name 2})+2)+

                (LEN({Style Name 3})+2)+

		(LEN({Style Name 4})+2)

            )-FIND(

                '\n',

                {Style Name}&'\n',

                (LEN({Style Name 1})+2)+

                (LEN({Style Name 2})+2)+

 		(LEN({Style Name 3})+2)

                )-1

            )

OK, mighta gotten it:

IF( {Comma Count}>=5, 

MID( {Style Name}&'\n', 

FIND( '\n', {Style Name}, 

(LEN({Style Name 1})+2)+ 

(LEN({Style Name 2})+2)+ 

(LEN({Style Name 3})+2) )+1, 

FIND( '\n', {Style Name}&'\n', 

(LEN({Style Name 1})+2)+ 

(LEN({Style Name 2})+2)+ 

(LEN({Style Name 3})+2)+ 

(LEN({Style Name 4})+2) )

-FIND( '\n', {Style Name}&'\n', 

(LEN({Style Name 1})+2)+ 

(LEN({Style Name 2})+2)+ 

(LEN({Style Name 3})+2) )-1 ) )

IF( {Comma Count}>=5, 

MID( {Style Name}&'\n', 

FIND( '\n', {Style Name}, 

(LEN({Style Name 1})+2)+ 

(LEN({Style Name 2})+2)+ 

(LEN({Style Name 3})+2) )+1, 

FIND( '\n', {Style Name}&'\n', 

(LEN({Style Name 1})+2)+ 

(LEN({Style Name 2})+2)+ 

(LEN({Style Name 3})+2)+ 

(LEN({Style Name 4})+2) )

-FIND( '\n', {Style Name}&'\n', 

(LEN({Style Name 1})+2)+ 

(LEN({Style Name 2})+2)+ 

(LEN({Style Name 3})+2) )-1 ) )

Yes, that works for the fifth field. Here’s the syntax for the sixth:

IF( {Comma Count}>=6, 

MID( {Style Name}&'\n', 

FIND( '\n', {Style Name}, 

(LEN({Style Name 1})+2)+ 

(LEN({Style Name 2})+2)+ 

(LEN({Style Name 3})+2)+ 

(LEN({Style Name 4})+2) )+1, 

FIND( '\n', {Style Name}&'\n', 

(LEN({Style Name 1})+2)+ 

(LEN({Style Name 2})+2)+ 

(LEN({Style Name 3})+2)+ 

(LEN({Style Name 4})+2)+ 

(LEN({Style Name 5})+2) )

-FIND( '\n', {Style Name}&'\n', 

(LEN({Style Name 1})+2)+ 

(LEN({Style Name 2})+2)+ 

(LEN({Style Name 3})+2)+ 

(LEN({Style Name 4})+2) )-1 ) )

IF( {Comma Count}>=5, 

MID( {Style Name}&'\n', 

FIND( '\n', {Style Name}, 

(LEN({Style Name 1})+2)+ 

(LEN({Style Name 2})+2)+ 

(LEN({Style Name 3})+2) )+1, 

FIND( '\n', {Style Name}&'\n', 

(LEN({Style Name 1})+2)+ 

(LEN({Style Name 2})+2)+ 

(LEN({Style Name 3})+2)+ 

(LEN({Style Name 4})+2) )

-FIND( '\n', {Style Name}&'\n', 

(LEN({Style Name 1})+2)+ 

(LEN({Style Name 2})+2)+ 

(LEN({Style Name 3})+2) )-1 ) )

I wanted to apologize for not getting back to you; I’m guessing I experienced a browser crash part-way through my reply, since when I opened this thread, I was greeted by an edit window containing a partial response. (Anyone know if Discourse allows one to display any unfinished drafts on file?) I see you didn’t need me, thankfully.

I guess I’m one of those Santayana said were condemned to repeat the past, as I essentially recreated this post and base from scratch a couple of weeks ago. I haven’t tried a point-by-point comparison, but I have a feeling the newer version is a slightly more elegant solution (or, at least, explained more nicely).

LEN({URLString})-LEN(SUBSTITUTE({URLString},',',''))+1

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

            )

    )

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

            )

IF(

    {URLCount}>=1,

    MID(

        {URLString}&',',

        1,

        FIND(

            ',',

            {URLString}&',',

            1

            )-1

        )

    )

IF(

    {URLCount}>=2,

    MID(

        {URLString}&',',

        FIND(

            ',',

            {URLString},

            0

            )+1,

        FIND(

            ',',

            {URLString}&',',

            (LEN({URL1})+2)

            )-FIND(

                ',',

                {URLString}&',',

                0

                )-1

            )

    )

Note in {URL4} example there is one closing ‘)’ missing at the very bottom.

I posted an alternative solution in this topic.

I am also posting the formulas here in order to

• acknowledge that this topic was the inspiration for my solution


• highlight aspects of my refactoring that would have cluttered up the other topic


• make it easier to continue this conversation (where the formula writers are)

While this solution was inspired by W_Vann_Hall’s post, the formulas are actually quite different.

• This solution does not need a field to count the number of items in the list, which reduces the complexity of the formulas. (The initial IF statement is not needed.)


• It uses a combination of LEFT and RIGHT functions, instead of the MID function. When building the functions, it was easier for me to mentally picture using the RIGHT function to identify everything remaining in the string, and then using the LEFT function to pick off the first item.


• It also reduces the overall amount and complexity of code by
  
  
  
  • Using the CONCATENATE function instead of summing multiple LEN functions.
  
  
  • Subtracting a single number that increases for each item, rather than repeatedly adding 2 for each previous item.
  
  
  
  

Here is a screen shot of the sample base.

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}:

• add the next item to both of the CONCATENATE functions


• subtract two more from the results of the LEN functions (e.g., for the 5th item, subtract 8; for the 6th item subtract 10, etc.) The number to subtract is determined by (2 x the item number) - 2. (Note you cannot put this in the actual formula because the item number is not a field.)

Thank you, @kuovonne for your solution.

Do you think it may be extrapolated for undefined number of Items (max 10) and with the need to put each of them separately from the new line (after " \n ") in a long text field?