Split or Parse Formula

Hi All

I am trying to spilt a multi select drop down column into two individual columns -

I have tried several options but just cannot get them working

=IF(ISERROR(FIND(",", [Survey Type]@row)), [Survey Type]@row, LEFT([Survey Type]@row, FIND(",", [Survey Type]@row) - 1))

=LEFT([Survey Type]@row, FIND(",", [Survey Type]@row + ",") - 1)

=MID(A1, 1, FIND(",", A1) - 1)

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

Try this:

=IFERROR(MID(text, FIND("|", SUBSTITUTE(SUBSTITUTE("~" + [Survey Type]@row + "~", CHAR(10), "~"), "~", "|", 1)) + 1, FIND("|", SUBSTITUTE(SUBSTITUTE("~" + [Survey Type]@row + "~", CHAR(10), "~"), "~", "|", 2) - (FIND("|", SUBSTITUTE(SUBSTITUTE("~" + [Survey Type]@row + "~", CHAR(10), "~"), "~", "|", 1)) + 1)), "")

The above will pull the first selection. You will notice three bold numbers in the order of 1 / 2 / 1. These will need adjusted to pull the 2nd, 3rd, etc. entries. First and last numbers will be the entry number, and the middle number is basically entry number plus one. So to pull the second entry, you would use 2 / 3 / 2. Third entry is 3 / 4 / 3, fourth entry is 4 / 5 / 4, so on and so forth.

Thanks Paul

Happy to help. 👍️

Hi Paul

I cant seem to get the formula working

jmyzk_cloudsmart_jp

Hi @Susan van Niekerk

The text in the formula ("=IFERROR(MID(text, ") needs to be replaced with the actual text that has "~" before and after itself.

=IFERROR(MID("~" + [Survey Type]@row + "~", FIND("|", SUBSTITUTE("~" + SUBSTITUTE([Survey

Though essentially the same method as Paul's, I prefer replacing the repetitive expression in the formula with a helper column, as shown in the image below, as [Prep List].

[Prep List] =[DL1]# + SUBSTITUTE([Multiple List]@row, CHAR(10), [DL1]#) + [DL1]#

https://app.smartsheet.com/b/publish?EQBCT=f9f887fd0b7e4609b95979e38ba8571c

That way, the formula becomes easier to understand.

I also prefer to use the Sheet Summary fields to set the delimiter characters, such as "~" and "|".

L1, L2… formulas

[L1] =IFERROR(MID([Prep List]@row, FIND([DL2]#, SUBSTITUTE([Prep List]@row, [DL1]#, [DL2]#, 1)) + 1, FIND([DL2]#, SUBSTITUTE([Prep List]@row, [DL1]#, [DL2]#, 2)) - FIND([DL2]#, SUBSTITUTE([Prep List]@row, [DL1]#, [DL2]#, 1)) - 1), "")

[L2] =IFERROR(MID([Prep List]@row, FIND([DL2]#, SUBSTITUTE([Prep List]@row, [DL1]#, [DL2]#, 2)) + 1, FIND([DL2]#, SUBSTITUTE([Prep List]@row, [DL1]#, [DL2]#, 3)) - FIND([DL2]#, SUBSTITUTE([Prep List]@row, [DL1]#, [DL2]#, 2)) - 1), "")

For example, if the [Prep List] is "~BAS~EIA~ESA~Fauna~Flora~HRA~SRE", the [L2]'s MID function to get EIA means as follows;

text is [Prep List]@row or "~BAS~EIA~ESA~Fauna~Flora~HRA~SRE
start_position is the place of second "~" or before EIA plus 1.
num_chars is 3rd "~" place minus 2nd "~" place minus 1.

MID(text, start_position, num_chars)

Paul Newcome

@Susan van Niekerk GIve this a try:

=IFERROR(MID([Survey Type]@row, FIND("|", SUBSTITUTE(SUBSTITUTE("~" + [Survey Type]@row + "~", CHAR(10), "~"), "~", "|", 1)) + 1, FIND("|", SUBSTITUTE(SUBSTITUTE("~" + [Survey Type]@row + "~", CHAR(10), "~"), "~", "|", 2) - (FIND("|", SUBSTITUTE(SUBSTITUTE("~" + [Survey Type]@row + "~", CHAR(10), "~"), "~", "|", 1)) + 1)), "")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.