Formula to pull constant number string from variable text strings

Paul Newcome

jmyzk_cloudsmart_jp

Jan 22, 2024

Hi @ljpettr1

Referring to @Paul Newcome 's idea of using "some way off the wall symbol that I know will never be found in the string", I have updated the sheet I showed you earlier.

This eliminates the m#, s0,s1, s,20 helper columns.

The expressions are now shorter, which may improve performance, if only slightly. However, the previous formula works fine, so it depends on whether you want to take the trouble to update it.

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

https://us.v-cdn.net/6031209/uploads/OQTNU5O8BH96/image.png

[List+] =SUBSTITUTE([Multiple Dropdown]@row, CHAR(10), DLM#) + DLM#

[n1] =LEFT([List+]@row, FIND(DLM#, [List+]@row) - 1)

[n2] =IFERROR(IF(ISTEXT([n1]@row), LEFT(SUBSTITUTE([List+]@row, JOIN([n1]@row:[n1]@row, DLM#) + DLM#, ""), FIND(DLM#, SUBSTITUTE([List+]@row, JOIN([n1]@row:[n1]@row, DLM#) + DLM#, "")) - 1)), "")

[n3] =IFERROR(IF(ISTEXT([n2]@row), LEFT(SUBSTITUTE([List+]@row, JOIN([n1]@row:[n2]@row, DLM#) + DLM#, ""), FIND(DLM#, SUBSTITUTE([List+]@row, JOIN([n1]@row:[n2]@row, DLM#) + DLM#, "")) - 1)), "")

[n4] =IFERROR(IF(ISTEXT([n3]@row), LEFT(SUBSTITUTE([List+]@row, JOIN([n1]@row:[n3]@row, DLM#) + DLM#, ""), FIND(DLM#, SUBSTITUTE([List+]@row, JOIN([n1]@row:[n3]@row, DLM#) + DLM#, "")) - 1)), "")

DLM# is a rare character chosen in the Sheet Summary field. (Example: "¬")

• ¬
• §
• ¶
• Ñ
• ~

Hi @Paul Newcome

I have devised a way to Extract the list from the Multiple Dropdown List using your idea of replacing delimiter such as CHAR(10), ",", etc. with generally unused characters.

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

https://us.v-cdn.net/6031209/uploads/ZAN7ZVZC73A7/image.png

List+

The [List+] column's formula is the following.

=SUBSTITUTE([Multiple Dropdown]@row, CHAR(10), DLM#) + DLM#

The + DLM# at the end facilitates retrieval of the last Item.

Items are retrieved in a structure, LEFT(TEXT, FIND(DLM#, TEXT)-1), but if the DLM# is to the right of the last Item, the last Item can also be retrieved using the same form of expression.

Last Item¬.

=LEFT("Last Item¬", FIND("¬", "Last Item¬") - 1)

output: Last Item

For example, if the following items are selected in the Multiple Dropdown List, then the formula will produce "Bangkok¬Buenos Aires¬London¬Los Angeles¬New York City¬Paris¬".

• Bangkok
• Buenos Aires
• Bangkok Buenos Aires
• Los Angeles
• New York City
• Paris

To get the first Bangkok in this "Bangkok¬Buenos Aires¬London¬Los Angeles¬New York City¬Paris¬" example

Text = "Bangkok¬Buenos Aires¬London¬Los Angeles¬New York City¬Paris¬", you can use a formula of the following form

LEFT(TEXT, FIND(DLM#, TEXT)-1)

OR

=LEFT("Bangkok¬Buenos Aires¬London¬Los Angeles¬New York City¬Paris¬", FIND("¬", "Bangkok¬Buenos Aires¬London¬Los Angeles¬New York City¬Paris ¬") - 1)

Stepwise Substitution

After the second stage, items can be sequentially obtained by applying the above formula LEFT(TEXT, FIND(DLM#, TEXT)-1) to the TEXT, excluding the Item + DLM# obtained in the previous stage.

Use the following formula to remove items up to the previous stage + DLM#.

n th Item

SUBSTITUTE([List+]@row, JOIN([n1]@row:[n-1]@row, DLM#) + DLM#, "")

For example, "Bangkok¬Buenos Aires¬London¬Los Angeles¬New York City¬Paris¬" is replaced by "Buenos Aires¬London¬ Los Angeles¬New York City¬Paris¬" in the second formula, and LEFT(TEXT, FIND(DLM#, TEXT)-1) can be used to extract Buenos Aires.

2nd.

SUBSTITUTE([List+]@row, JOIN([n1]@row:[n1]@row, DLM#) + DLM#, "")

output: "Buenos Aires¬London¬Los Angeles¬New York City¬Paris¬"

After the third, the structure of the expression is simplified because we only increment [n1] on the right side of JOIN([n1]@row:[n1]@row).

3rd.

SUBSTITUTE([List+]@row, JOIN([n1]@row:[n2]@row, DLM#) + DLM#, "")

output: "London¬Los Angeles¬New York City¬Paris¬"

4th.

SUBSTITUTE([List+]@row, JOIN([n1]@row:[n3]@row, DLM#) + DLM#, "")

output: "Los Angeles¬New York City¬Paris¬"

Whole formula (4th item example)

[L4] =IFERROR(IF(ISTEXT([n3]@row), LEFT(SUBSTITUTE([List+]@row, JOIN([n1]@row:[n3]@row, DLM#) + DLM#, "")), FIND(DLM#, SUBSTITUTE([List+]@row, "") JOIN([n1]@row:[n3]@row, DLM#) + DLM#, "")) - 1))), "")

Supplemental Explanation

IFERROR(, ""))

We will remove [Item + DLM#] obtained in the previous step, and if it becomes blank and FIND results in error, it is the end, so return blank ""

IF(ISTEXT([nx]@row),")

When the blank space enters the previous stage in the above IFEEOR, JOIN([n1]@row:[nx]@row, DLM#) + DLM# adds DLM# for the blank space as shown in the example below. , so add an IF expression to limit it only if the previous Item is not blank and is text.

"Los Angeles¬New York City¬Paris¬¬¬¬¬"

My method using the MID function does work in a stepped fashion and can be used to find the first through the last instance without using the extra [N#] columns and without needing different formulas for different steps or referencing different cells. You only need to reference the multi-select cell with my formula. The formula in my post that you are referring to is actually specifically designed for a multi-select dropdown without any modification necessary other than specifying which instance you want to pull (1, 2, 1 / 2, 3, 2 / 3, 4, 3 / etc.).

Using my method means less unique cell references and as such should improve performance quite a bit since it cuts down on the number of needed columns in addition too the number of unique cell references.

Paul Newcome

@Lucas Rayala It isn't the SUBSTITUTE function that reorders the list. It is because a multi-select field is stored on the back end in alphabetical order. I will have to take a look through my notes to see how I got through this reliably, but I am fairly certain I usually end up converting it to a text string in a text/number column.

Lucas Rayala

Paul Newcome

Jan 23, 2024

@Lucas Rayala It isn't the SUBSTITUTE function that reorders the list. It is because a multi-select field is stored on the back end in alphabetical order. I will have to take a look through my notes to see how I got through this reliably, but I am fairly certain I usually end up converting it to a text string in a text/number column.

That makes sense! And that's actually a key reason to convert it prior to parsing it (if even just for clarity's sake). I've parsed these straight from the list before using a CHAR(10) as part of the FIND criteria and the results always came through a little wonky because they weren't in the order I was expecting. It's too bad you can't keep that original order -- I don't seem to see a way, anyway. Even simply reflecting a list, =list@row, puts them in alpha-numeric order.

Paul Newcome

@Lucas Rayala I have always made sure my column properties had the various options listed in alpha-numeric order anyway. Haha. I have only run into it a few times where I had to work with a client created list in the order they wanted.

Lucas Rayala

Paul Newcome

Jan 23, 2024

@Lucas Rayala I have always made sure my column properties had the various options listed in alpha-numeric order anyway. Haha. I have only run into it a few times where I had to work with a client created list in the order they wanted.

I sometimes have large lists, of which only 2 or 3 items are chosen on a regular basis. In those instance, I usually add those two items to the top (as well as the general list). That makes it easier for the user to grab what they need. I think that's why I've run into this.

Paul Newcome

@Lucas Rayala That makes sense. Usually in those cases I will start them out with a letter or number such as as "01. Option C", "02. Option E", "03. Option A", so on and so forth, but of course that's not always the preference.