How can I get a multi-select dropdown column to populate with comma separation?

Bill in Ohio

I have a two different columns that are populating differently, just using simple formulae: (I took out all the error checking to make it simple until I can get it to work)

=[Name]@row, where [Name] is a Contact list, allowing multiple contacts per cell, and

=[Department]@row, where [Department] is a Dropdown list, also allowing multiple values per cell. There are 29 values to select from.

The [Name] can have multiple names assigned to it, and when there are multiple names I get them appearing in the formula column like this:

Able Bobsen, Cammie Davis, Edward Franklin

(Notice the "," between each value?)

The [Department] can also have multiple values, but when there are, the formula cell shows like this:

Administration Human Resources Information Systems

I am trying to output the data to a nice Stakeholder sheet, and eventually other project information reports, such as Departments involved with the project. The names work great, I have several different sheets where I can link in the users, stakeholders, etc, with a nice list of names.

I can't use Administration Human Resources Information Systems anywhere - this is too hard for someone to interpret. I would like it to be formatted with commas in-between them:

Administration, Human Resources, Information Systems

I have looked through the forums for an answer, but can't seem to find it anywhere, so I am certain that I am using the wrong keywords to find the answer. I'm sure someone has using these helper columns to present the data. The columns are in the same sheet, so it seems that it should be simple, but the solutions I have found suggest testing for each value, which with 29 options seems way too complicated.

Thank you for ending my frustration if you can!

Paul Newcome

You have two options…

The default delimiter in a multi-select dropdown is a line break. This means if you apply text wrapping to the column, it should put each entry on a new line within the same cell.

If you definitely want the commas though for consistent formatting, you would use this:

=SUBSTITUTE(Department@row, CHAR(10), ", ")

Paul Newcome

You have two options…

The default delimiter in a multi-select dropdown is a line break. This means if you apply text wrapping to the column, it should put each entry on a new line within the same cell.

If you definitely want the commas though for consistent formatting, you would use this:

=SUBSTITUTE(Department@row, CHAR(10), ", ")

Pauline J

Hello @Bill in Ohio That's the same result I get when setting up a dummy sheet — the drop-down lists do not include the comma and space.

In my example, the Lists column is what happens when using your formula, the second column eliminates the last comma, using a formula to remove the last character in the Departments column, regardless of the number of entries.

The only down side is you need to add a comma to the end of each row in your drop-down list, but once you have the helper column setup, convert the NoComma column formula into a column formula, and it will parse every row that is added to your sheet.

Departments Column — Choose from the Drop-down list
Lists Column Formula is =Departments@row
NoComma Column Formula is =LEFT(Lists@row, LEN(Lists@row) - 1)

No IF tests involved — you just need to be OK with commas in your raw data (Departments Column). I hope that helps!

Pauline

Bill in Ohio

Thank you @Paul Newcome - That is what I was looking for! I never would have found that - I was looking in the wrong direction.

@Pauline J - I do like that suggestion, except that to keep the data clean in other lookups/references, I would have the comma as you suggested. I'm not sure that I want to do that. I can see different applications where this would work though. Thank you for the suggestion.

Paul Newcome

Happy to help. 👍️

Pauline J

@Bill in Ohio no worries! @Paul Newcome had the better and more elegant solution — and I learned something new too. That's what I appreciate most about this community, people are willing to share and we all get better! 🙂

Kayla H.

@Paul Newcome is there a way to turn a list separated by a delimiter into a multiple select drop down column? Essentially doing the same thing as in this original post, but in reverse?

Paul Newcome

@Kayla H. Yes. You would simply swap the last two pieces of the SUBSTITUTE function so that the delimiter being used is replaced with CHAR(10).

Kayla H.

@Paul Newcome great thank you!