Split or Parse Formula

Susan van Niekerk

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)

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.

Susan van Niekerk

Thanks Paul

Paul Newcome

Happy to help. 👍️

Susan van Niekerk

@Paul Newcome

Hi Paul

I cant seem to get the formula working

=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)), "")

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)), "")