Help

Re: Formula to Separate Concatenated Items?

3320 3
cancel
Showing results for 
Search instead for 
Did you mean: 
Arlo_Haskell
7 - App Architect
7 - App Architect

Does anyone know a way to separate items from a concatenated string?

I have a table that I’ve used to make a schedule of events for a conference. Each presenter makes multiple appearances. Using Zapier and WebMerge I plan to produce a print-ready manifest for each presenter that will show their personalized schedule, along with hotel, flight, and contact information from other tables.

Problem is, when I look up the start time of their presentations I get ALL the start times in a concatenated string like this:

1/13/2017 10:35am, 1/14/2017 9:40am, 1/14/2017 12:20pm

I’d like a formula that basically says “find and display first item from string”, “find and display second item from string” etc… Then I could create fields for “Start Time 1,” “Start Time 2”, match them up with corresponding End Time fields, and get it over to WebMerge and in print.

Many many thanks to anyone who can help point me in the right direction here. :eyeglasses:

26 Replies 26

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:

deconcatenate_example
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
            )