How to parse out drop down selections into separate cells

Hello Community

I created a form with multiple drop down options. I need to parse out those options so each one shows in a separate cell. I figured out the first one but cannot find the correct formula for the second, third cells, etc. Any help is appreciated.

Below is the formula i used to parse out the first item (thanks to Paul H who answered on a different post), which works.

=IF(FIND(CHAR(10), [DIVISION]@row) > 0, LEFT([DIVISION]@row, FIND(CHAR(10), [DIVISION]@row, 1) - 1), [DIVISION]@row)

2024-01-13_19-40-26.png

Find more posts tagged with

Parsing

Accepted answers

All comments

jmyzk_cloudsmart_jp

Hi @Rob N

The demo solution below was created by modifying a sheet that creates a single contact list from a multiple contact list. (Therefore, it is made to support up to 20 lists.)

As shown in your formula, we need to find the position of CHAR(10), so we systematically obtain the positions up to 20. ([m#] =COUNTM([Multiple Name]@row) <=20)

[c0] =0

[c1] =IF([m#]@row > 1, FIND(CHAR(10), [Multiple Name]@row, [c0]@row + 1))

[c2] =IF([m#]@row > 2, FIND(CHAR(10), [Multiple Name]@row, [c1]@row + 1))

Using that position, I use an expression to get each list with the same pattern of expressions.

[n1] =IF([m#]@row = 1, [Multiple Name]@row, IF([c1]@row > [c0]@row, MID([Multiple Name]@row, [c0]@row + 1, [c1]@row - [c0]@row), IF([c1]@row < [c0]@row, MID([Multiple Name]@row, [c0]@row + 1, LEN([Multiple Name]@row) - [c0]@row + 1))))

[n2] =IF([c2]@row > [c1]@row, MID([Multiple Name]@row, [c1]@row + 1, [c2]@row - [c1]@row), IF([c2]@row < [c1]@row, MID([Multiple Name]@row, [c1]@row + 1, LEN([Multiple Name]@row) - [c1]@row + 1)))

[n3] =IF([c3]@row > [c2]@row, MID([Multiple Name]@row, [c2]@row + 1, [c3]@row - [c2]@row), IF([c3]@row < [c2]@row, MID([Multiple Name]@row, [c2]@row + 1, LEN([Multiple Name]@row) - [c2]@row + 1)))

As you can see, you need to increment the number following to c after [n2].

You can test the sheet by clicking the URL below, as I published the sheet as editable. If you want a copy of the sheet, please get in touch with me at info@cloudsmart.jp.

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

Column Formulas

I omitted c5-c18 and n5-n18.

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

Rob N

@jmyzk_cloudsmart_jp thank you for your quick reply and detailed explanation. Unfortunately, I was not able to use your in depth formulas as my smartsheet knowledge is pretty basic. I am learning about M# and following your guidance but I unfortunately have not been able to match it up. I am confident its 100% user error and not your formula. I will let you know once I arrive at the solution. Thank you again

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.