Who could use a PARSE function?

Paul Newcome

So I have been trying to think of how to best word it, and trying to help someone else with a solution led me down the rabbit hole I needed.

How about a PARSE function.

=PARSE(range, selection number, delimiter)

Use case would be plentiful, but a couple of examples would be...

A. You want to search a range for the second choice in a multi-select column. As it stands, you would either need to parse out the data or use a series of MID and FIND functions to search on the CHAR(10) delimiter to pull that selection, or you could hard code that selection which means having to change it up for a number of formulas, or...

You could use the PARSE function and just have something to the effect of...

=COUNTIFS([Other Column]:[Other Column], CONTAINS(PARSE([Multi-Select Column]@row, 2, CHAR(10)), @cell))

B. In order to cut down on cells used on a cross sheet reference, you are using the method of joining the data on the source sheet, pulling it to the target sheet, then using a complex set of formulas to parse the data back out. The JOIN function you used to pull it all together is using a forward slash "/" as a delimiter. When you pull the string to the target sheet, instead of having to set up all of the extra formulas and helper columns and rows, you could use the PARSE statement like this...

=PARSE([String Column]$1, 1, "/")

=PARSE([String Column]$1, 2, "/")

so on and so forth.

I am going to be submitting a product enhancement request for this, but anyone else who thinks it could be useful, feel free to submit one of your own or provide feedback/tweaks/suggestions/etc here.

Paul Newcome

@Genevieve P @Mike Wilday @Andrée Starå @Alejandra

I have seen all of you providing regular solutions here in the Community, so I figured you would all have some kind of opinion on an enhancement like this.

Any thoughts or suggestions here? Kind of fleshing out some ideas that hopefully Smartsheet can use to possibly make our lives easier maybe. 🤞

Paul Newcome

Here is my Enhancement Request...

Having a function that is able to automatically parse data based on a selection number and a specified delimiter would be extremely useful. Use cases include being able to specify (for example) the third selection in a multi-select column or a much easier way to parse out a text string that was pulled together using a JOIN function.

My ideas for the function itself would be something along the lines of

=PARSE(range, selection number, delimiter)

range: A range, cell reference, or text string

selection number: This would indicate which selection in the string you want to pull

delimiter: This tell the function what is being used as a delimiter

Right now if I wanted to pull the second selection in a multi-select column, I would have to use a MID statement with a series of FIND statements to establish the starting point and how many characters to pull. Just to find the starting point alone is...

=FIND(CHAR(10), [Multi-Select Column]@row, FIND(CHAR(10), [Multi-Select Column]@row) + 1) + 1

Finding the third CHAR(10) is

=FIND(CHAR(10), [Multi-Select Column]@row, FIND(CHAR(10), [Multi-Select Column]@row, FIND(CHAR(10), [Multi-Select Column]@row) + 1) + 1)

Then we subtract the first FIND from the second FIND to establish how many characters to pull and then drop that into a MID statement.

And that's just to pull the SECOND selection. Each selection after that becomes more and more complicated or requires multiple rows/columns of similar formulas mixing in a few SUBSTITUTE functions and whatnot.

 So to pull the second selection using a MID statement:

=MID([Multi-Select Column]@row, FIND(CHAR(10), [Multi-Select Column]@row, FIND(CHAR(10), [Multi-Select Column]@row) + 1) + 1, (FIND(CHAR(10), [Multi-Select Column]@row, FIND(CHAR(10), [Multi-Select Column]@row, FIND(CHAR(10), [Multi-Select Column]@row) + 1) + 1) - FIND(CHAR(10), [Multi-Select Column]@row, FIND(CHAR(10), [Multi-Select Column]@row) + 1) + 1))

Or using a PARSE function:

=PARSE([Multi-Select Column]@row, 2, CHAR(10))

If parsing could be done on the back-end through a function such as this, a lot of solutions could become much easier.

COMMUNITY POST: https://community.smartsheet.com/discussion/66710/who-could-use-a-parse-function/p1?new=1

Mike Wilday

This could be really useful. I can envision it looking or acting similarly to the SPLIT function in Google Sheets. Or if that type of functionality isn't possible, maybe even creating an array. It would be totally useful to pull apart the data. I don't particularly have a use case for this with what we're doing in Smartsheets now but I do think that something of this nature is needed when you have a multi-select option. Some way to split out the data would be extremely useful.

Frank Falco

@Paul Newcome I like where you are going with this however as I stated in my original post (see below)

What would be great is to use the items in the multi-select as parameters in functions.

Such as using AND([Multi-sel col]@row) or OR([Multi-sel col]@row) and using thos in COUNTIFS, SUMIFS, AVG etc

That is what I was trying to do

https://community.smartsheet.com/discussion/66705/count-of-dropdown-multi-select-items-in-another-sheet

Paul Newcome

@Frank Falco Correct. In your original post you wanted to count how many times (for example) "Step 1" or "Step 2" were found.

Using my idea of a PARSE function, it would look similar to

=COUNTIFS({Other Sheet Range}, OR(CONTAINS(PARSE([Multi-Select]@row, 1, CHAR(10)), @cell), CONTAINS(PARSE([Multi-Select]@row, 2, CHAR(10)), @cell)))

Your comment:

"It would be useful to be able to address each element or as in this case use the elements as a virtual or condition in a formula"

The PARSE function would allow this, but I expanded it a little beyond exactly what you needed to allow for additional flexibility and more use cases instead of being strictly limited to a multi-select column.

Paul Newcome

@Mike Wilday I've never used Google Sheets, but I did go ahead and Google the function. Yes. Something very similar to that. I like how it also provides the option to leave out empty text. The addition I think would make it even more useful is the "selection number". Being able to tell it to not only parse out the data but to also pull a specific section. That way we don't have to include extra columns for the data to parse across and then use a cell reference. We can just use a number right there in the function.

My thought process is more along the lines of using the MID/FIND combo to pull a specific section, but using it to parse an entire string out across a row (while also very helpful) is secondary. Maybe "PARSE" isn't the best thing to call it. Maybe "BETTERMID"? 😂

Genevieve P.

Very interesting idea! I can definitely see where this could come in handy. I'll submit my own Enhancement Request for this as well.

PS. Mike's gif made my day 😂

Paul Newcome

Hahaha! I loved the GIF too. All of them are ready for business except for the one off to the side who is just ready to play. Haha!

Ezra

I think I see where you're going with this, but I think the "Selection Number" and "Delimiter" are probably not the best ways to go about it.

I think you'd have to use this Parse function inside of another to get anything out of it... IF, IFS, COUNTIF, etc...

• The first parameter would be the range/cell
• Next would be what it's searching for.. either hardcoded "Foo" (or multiple searches separated by semi-colons "Foo";"Bar";"foo";"bar";"Foo Bar";"foo bar" etc..) or reference a cell or range [Selection Items]1:[Selection Items]7
• How it handles successful results would be the third parameter.. found "Foo" , "Bar" and "Foo Bar", so:
  • 0 (default) = return TRUE if any are found
  • 1 = return TRUE only if all are found
  • 2 = return count of all matching search items in range(s)
  • 3?
  • 4?
• Parameters for errors, blanks and such could be the fourth and/or options

To piggy back on this....

I still think we need a way to build Dynamic selection and multi-selection columns.. for example a range in another sheet with column [Color Name] that had three color names in the first three rows, followed by a blank row, would give you Red, Orange and Yellow for your drop down selections.

Later adding a few more color names to that first sheet would give you: Red, Orange, Yellow, Green, Blue, Indigo and Violet

Paul Newcome

@Ezra My original thought behind the PARSE function was not necessarily a true/false value, but the ability to essentially pull text from a string. Similar to how a MID function works, but based on a unique delimiter instead of having to try to specify character numbers.

Using that logic, being required to nest it inside of another function defeats the purpose entirely of what I would hope to accomplish.

To pull a true/false for specific text within a longer string, you can just use HAS or CONTAINS or even IF(FIND(..........) > 0, ...............)

I do understand what you are saying, but I think that you would be limiting it to the point of not being very useful and certainly not nearly as capable as where we were going with it.

Additionally... I am not sure what your dynamic columns has to do with it. Can you explain how that would tie in?

Mike Wilday

And in my mind I see the parse function in cases where the string is the multi-select column, even to split that data up into separate columns for processing.

Paul Newcome

@Mike Wilday

That was originally where I started with it, but why limit it to multi-select columns? If we can specify a delimiter, then it increases the flexibility of the function and allows for use on more than just one column type. Since we know the delimiter on multi-selects is CHAR(10), we wouldn't have to worry about missing out on that particular bit either.

Ezra

I think I see where you are going now.. before I thought you would be trying to find if a particular selection (like "Red" and "Blue") existed in a multi-select cell - which, yes, you can already do with existing formulas. I see now that you're trying to get a read-out of the second (or third or twenty-seventh..) selection from that multi-select cell..

for example: [Multi Color]$4 contains five selections:

• Red Yellow Green Blue Grey
• in a sheet summary or some particular cell, you need to know what the third selection is...
• =PARSE([Multi Color]$4,3,CHAR(10))
• would result in Green

If Red was not one of the selections, it would return Blue , and if that cell only had two selections, you would get an error.

I'd like to propose that the delimiter be optional, and not needed for some column types - if it's a multi-select dropdown, CHAR(10) would be assumed.. if a multi-select contact column, a comma would be the default delimiter... etc....

On that side-note, the whole "dynamic dropdown" idea has been a wishlist item for over five years, apparently. Using a separate sheet to build/manage the available dropdown selections would be amazingly helpful... especially for Control Center and such. I won't go into that further - don't want to confuse this thread.

Paul Newcome

@Ezra Yes. That is exactly it. Not just searching data but actually parsing it out. My initial vision was parsing out a string which is where the delimiter function came in.

It kind of feels like a mashup of JOIN, SMALL/LARGE, and MID.

JOIN allows us to specify delimiters.

SMALL/LARGE functions allow us to specify whether we want the first or third or twenty-seventh.

MID allows us to pull from the middle of a string.

I do like your idea of the delimiter being optional and having defaults built in based on the referenced column type, but at this point I personally would be ok with having to specify the delimiter every time. Having the rest of the function working would make it completely worth the extra step in my opinion. Hahaha.

JeremiahHorstick

+1

Your idea would allow us to evaluate multi-select column elements on an individual basis in a formula rather than the entire contents of the cell as a single string with the char10 delimiter.