Concatenate Existing Columns

I have an existing sheet with hundreds of lines of data. We recently decided we needed to combine two of the columns, but don't want to have to go through each line and combine them. Is there a formula I can use to copy the information from Column B into Column A with a space and comma in between?


If I use =JOIN(ColumnA:ColumnB," ") it adds everything selected into one cell. I need to differentiate between rows without having to set up an equation for each row.

Thanks!

Answers

  • John Jonassen
    John Jonassen ✭✭✭✭

    Concatenation is easier than one might think.

    =[ColumnA]+" "+[Column B]

    Omit the " " and one + if you don't need a space in between.

  • Kelly Drake
    Kelly Drake Overachievers Alumni

    Minor edit to the formula....put this into ColumnC and drag the formula the entire length of your sheet.

    = ColumnA@row + " " + ColumnB@row

    Kelly Drake (she/her/hers)

    STARBUCKS COFFEE COMPANY| business optimization product manager

  • @Kelly Drake I did a test on two cells in two rows and it says invalid operation. Thoughts?

  • is there any way I can concatenate in same column with existing value.

    Like Column


    ColumnA@row = ColumnA@row + " " + ColumnB@row,


    i want to record all the comment entered in column A in column B with previous values in column B.

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @alishah730

    There currently isn't a way to reference the current cell while typing a formula - you would need to add the display value into the formula. So, if your cell should show "Complete" + the other cell, you'd type:

    ="Complete" + " " + ColumnB@row

    However keep in mind to change that text you'll need to edit the formula.

    I'd recommend adding the formula in a new column, ColumnC, instead.

    Cheers,

    Genevieve

  • so we can not reference the same cell where putting the formula. then how it will keep track of all the data appended.

    if there is any work around could you please share an example?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @alishah730

    This is what it would look like if you put the formula in a third column:

    If this is not what you're looking to do, please post a screen capture of an example sheet with test data within it, showing what it is you'd like to achieve with a formula, but ensure you don't post any sensitive data.

    Thanks,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!