Extracting Multiple Data from one cell into multiple cells - same row

I have a smartsheet where I'd like to extract data from the LIST ALL occupants column and place them into Occupant 1, Occupant 2, etc. out to 13. I have this working for columns 1 and 2, but then it repeats, even though I change the +# in increments of 1… and if there are no additional names, I'd like it to be blank.

Occupant 1: =LEFT([List ALL Occupants and their Percent Usage]@row, FIND(", ", [List ALL Occupants and their Percent Usage]@row) - 1)

Occupant 2 and rest: =MID([List ALL Occupants and their Percent Usage]@row, FIND(", ", [List ALL Occupants and their Percent Usage]@row) + 2, 10)

I'm missing something and I'm sure it's something simple…any ideas? Please and thank you!

Lori

Find more posts tagged with

Accepted answers

All comments

heyjay

Here's a great solution by Paul. This may need a bit of tweaking depending on your delimeter.

https://community.smartsheet.com/discussion/comment/243743?utm_source=community-share&tab=all#Comment_243743

Paul Newcome

Here is a base formula you can use:

=MID("~" + [List ALL Occupants and their Percent Usage]@row + "~", FIND("~", SUBSTITUTE("~" + [List ALL Occupants and their Percent Usage]@row + "~", ", ", "~", 1)) + 1, FIND("~", SUBSTITUTE("~" + [List ALL Occupants and their Percent Usage]@row + "~", ", ", "~", 2)) - (FIND("~", SUBSTITUTE("~" + [List ALL Occupants and their Percent Usage]@row + "~", ", ", "~", 1)) + 1))

In the formula above, you will see three bold numbers. From left to right the are 1, 2, 1. This will pull the first occupant. Changing these three numbers to 2, 3, 2 will pull the second occupant. 3, 4, 3 will pull the third, so on and so forth.

Second:

=MID("~" + [List ALL Occupants and their Percent Usage]@row + "~", FIND("~", SUBSTITUTE("~" + [List ALL Occupants and their Percent Usage]@row + "~", ", ", "~", 2)) + 1, FIND("~", SUBSTITUTE("~" + [List ALL Occupants and their Percent Usage]@row + "~", ", ", "~", 3)) - (FIND("~", SUBSTITUTE("~" + [List ALL Occupants and their Percent Usage]@row + "~", ", ", "~", 2)) + 1))

Third:

=MID("~" + [List ALL Occupants and their Percent Usage]@row + "~", FIND("~", SUBSTITUTE("~" + [List ALL Occupants and their Percent Usage]@row + "~", ", ", "~", 3)) + 1, FIND("~", SUBSTITUTE("~" + [List ALL Occupants and their Percent Usage]@row + "~", ", ", "~", 4)) - (FIND("~", SUBSTITUTE("~" + [List ALL Occupants and their Percent Usage]@row + "~", ", ", "~", 3)) + 1))

Once you have confirmed this is working, you can wrap it in an IFERROR to output blanks if there are not that many occupants.

=IFERROR(MID("~" + [List ALL Occupants and their Percent Usage]@row + "~", FIND("~", SUBSTITUTE("~" + [List ALL Occupants and their Percent Usage]@row + "~", ", ", "~", 3)) + 1, FIND("~", SUBSTITUTE("~" + [List ALL Occupants and their Percent Usage]@row + "~", ", ", "~", 4)) - (FIND("~", SUBSTITUTE("~" + [List ALL Occupants and their Percent Usage]@row + "~", ", ", "~", 3)) + 1)), "")

maineL

Should I put that huge formula all into one cell? Should I make this a helper sheet? Just want to be sure I'm following. Thank you!

maineL

I'm getting an Invalid Value when pasting in the top formula.

Paul Newcome

Try this one:
=MID(SUBSTITUTE("," + [List ALL Occupants and their Percent Usage]@row + ",", ", ", ","), FIND("~", SUBSTITUTE(SUBSTITUTE("," + [List ALL Occupants and their Percent Usage]@row + ",", ", ", ","), ",", "~", 1)) + 1, FIND("~", SUBSTITUTE(SUBSTITUTE("," + [List ALL Occupants and their Percent Usage]@row + ",", ", ", ","), ",", "~", 2)) - (FIND("~", SUBSTITUTE(SUBSTITUTE("," + [List ALL Occupants and their Percent Usage]@row + ",", ", ", ","), ",", "~", 1)) + 1))

maineL

I tried this: =MID([List ALL Occupants and their Percent Usage]@row, FIND(", ", [List ALL Occupants and their Percent Usage]@row) + 2, 10) in the 2nd column (Occupant 2) and it worked. I also note that if I change the 10 to 0, it comes back blank…HOWEVER, when I try to carry this formula into Occupant 3 and change the +2 to +3, it still puts in Occupant 2. I'll try your formula now. Would this be in the Occupant 2 column?

maineL

This formula is working!!!! THANK YOU!

=MID(SUBSTITUTE("," + [List ALL Occupants and their Percent Usage]@row + ",", ", ", ","), FIND("~", SUBSTITUTE(SUBSTITUTE("," + [List ALL Occupants and their Percent Usage]@row + ",", ", ", ","), ",", "~", 2)) + 1, FIND("~", SUBSTITUTE(SUBSTITUTE("," + [List ALL Occupants and their Percent Usage]@row + ",", ", ", ","), ",", "~", 3)) - (FIND("~", SUBSTITUTE(SUBSTITUTE("," + [List ALL Occupants and their Percent Usage]@row + ",", ", ", ","), ",", "~", 2)) + 1))

Now, how would I get it so if there are no data for this, it returns a blank…

Paul Newcome

You would just need an IFERROR.

=IFERROR(MID(………………….)), "")

maineL

Thank you! So, I put this in and the column went blank…which is what I want, if it doesn't apply. The rows that had 4+ names in it are now not showing at all. I'm sure I'm missing something small…

=IFERROR(MID(SUBSTITUTE("," + [Occupant 1]@row + ",", ", ", ","), FIND("~", SUBSTITUTE(SUBSTITUTE("," + [Occupant 1]@row + ",", ", ", ","), ",", "~", 4)) + 1, FIND("~", SUBSTITUTE(SUBSTITUTE("," + [Occupant 1]@row + ",", ", ", ","), ",", "~", 5) - (FIND("~", SUBSTITUTE(SUBSTITUTE("," + [Occupant 1]@row + ",", ", ", ","), ",", "~", 4)) + 1))), "")

Paul Newcome

Are you able to provide a screenshot?

maineL

Sure:

Formula: =IFERROR(MID(SUBSTITUTE("," + [Occupant 1]@row + ",", ", ", ","), FIND("~", SUBSTITUTE(SUBSTITUTE("," + [Occupant 1]@row + ",", ", ", ","), ",", "~", 4)) + 1, FIND("~", SUBSTITUTE(SUBSTITUTE("," + [Occupant 1]@row + ",", ", ", ","), ",", "~", 5) - (FIND("~", SUBSTITUTE(SUBSTITUTE("," + [Occupant 1]@row + ",", ", ", ","), ",", "~", 4)) + 1))), "")

Paul Newcome

It is because your cell references are all pointing at [Occupant 1]@row instead of [List ALL Occupants and their Percent Usage]@row.

maineL

I'm a dope. Ok, I fixed it, but it's still showing blank, even for the ones that have data.

=IFERROR(MID(SUBSTITUTE("," + [List ALL Occupants and their Percent Usage]@row + ",", ", ", ","), FIND("~", SUBSTITUTE(SUBSTITUTE("," + [List ALL Occupants and their Percent Usage]@row + ",", ", ", ","), ",", "~", 4)) + 1, FIND("~", SUBSTITUTE(SUBSTITUTE("," + [List ALL Occupants and their Percent Usage]@row + ",", ", ", ","), ",", "~", 5) - (FIND("~", SUBSTITUTE(SUBSTITUTE("," + [List ALL Occupants and their Percent Usage]@row + ",", ", ", ","), ",", "~", 4)) + 1))), "")

Paul Newcome

Do you have an updated screenshot?

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.