Renaming (multiple) values from one column into another column

I am looking to rename certain values that I have in one of my columns. The original column contains values that are accepted in our database (intended for database input), and the new column would be the same value with a descriptor that is intended to be used by product managers. The second column should output all of the values in the first column, not just the first one.

I tried using an IF/AND/Contains formula but had no luck. There is a good chance that I was formatting it incorrectly.

Example. Desired output is column 2

Column 1 | Column 2

R R -Red

R,G R - Red, G - Green

R,G,B R -Red, G - Green, B - Blue

Tags:

Answers

  • Will.Parente
    Will.Parente ✭✭✭✭✭

    Shaun, am I understanding correctly this is what you are trying to do:

    Column 1 has existing date e.g. INPUT

    Column 2 will be filled in with a description e.g. DESCRIP

    You want Column 3 to bring these together into something that looks like INPUT-DESCRIP

    ---

    If that's right you can just click on the cell in Column 3 and type =Column1@row + "-" + Column2@row


  • Hi Will,

    I have been able to apply that when the cell contained a single value. In my situation, column 1 can contain multiple values and contains values from a dropdown list.

  • Will.Parente
    Will.Parente ✭✭✭✭✭

    Shaun,

    I see, I misunderstood the ask a bit.

    Ok - so what's the deal with your first column? How many options are available? Is the list restricted to list values only?

    I ask because what I am thinking is, if your first column doesn't have a lot of options and those options are restricted to list values, using helper columns and an embedded IF statement could be a solution:

    ---------

    HelperColumn1 =IF(HAS([Column1]:[Column1], "R"), "R - Red")

    HelperColumn2 =IF(HAS([Column1]:[Column1], "G"), "G - Green)

    -------------

    Finally, using helper columns, you would need another helper column with an embedded IF statement that looks something like this:

    OutputColumn =IF(COUNTM([Column1]:[Column1])=1, HelperColumn1, IF(COUNTM([Column1]:[Column1])=2, HelperColumn1 + ", " + HelperColumn 2, IF(COUNTM(Column1:Column2)=3, etc. etc.

    --------

    If you have a large list of items in Column1... I will need to take more time to think how to make it work OR hope another community member hops in with an elegant solution.

    What may help too, do you have any Premium Apps?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!