
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Dec 20, 2016 09:16 PM
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:
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 03, 2018 08:35 AM
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 ) )
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 03, 2018 10:28 AM
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 ) )

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Jul 13, 2018 05:00 AM
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).

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 20, 2019 01:20 PM
Note in {URL4} example there is one closing ‘)’ missing at the very bottom.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Feb 26, 2020 03:19 PM
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
andRIGHT
functions, instead of theMID
function. When building the functions, it was easier for me to mentally picture using theRIGHT
function to identify everything remaining in the string, and then using theLEFT
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 multipleLEN
functions. - Subtracting a single number that increases for each item, rather than repeatedly adding 2 for each previous item.
- Using the
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.)

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 03, 2020 01:41 PM
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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 03, 2020 01:49 PM
The formulas should work for far more than 10 items. Just keep following the pattern. I haven’t tried it for items with "\n ", but you could try it and let us know how it goes.
With the release of the new scripting block, I think that long workarounds like this will no longer be needed.

- « Previous
- Next »