Export to Excel so multi-select field is comma seperated

Andrea Prochaska
Andrea Prochaska ✭✭✭
edited 06/15/22 in Smartsheet Basics

I want to use Smartsheet to hold our eCommerce database so I can control field "answers" and make everything consistent using mulit-select (restricted answers).

The problem I have is when I export to Excel the field in not comma separated anymore which means our system isn't populating fields correctly. It is either entering the data without commas like the screen below, or just ignoring what is in the field all together and not updating.

I have many columns I need to be comma separated when exported so I am hoping there is a very easy fix for this. If not, I will be forced to figure this out in an excel sheet that does not have as many controls.

Answers

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Andrea Prochaska

    What I would do in this instance is add a helper Text column for each one of your multi-select columns to change the formatting in your Smartsheet first so that Excel can read the data correctly. These helper columns would use a formula to replace the spaces between selections with a comma, like so:

    =SUBSTITUTE([Multi Select]@row, CHAR(10), ", ")

    CHAR(10) is the space between multi-select values, what you designate at the end of the SUBSTITUTE function is what to add between values:

    Then your exported Excel will have a column with your values you can use to map.

    Cheers,

    Genevieve