Forum Discussion

BrokerJoey's avatar
BrokerJoey
Copper Contributor
Dec 05, 2022

Combing columns

I know this is a basic question, I am trying to combine columns while adding a space and the & sign.

 

Column A is the client's 1st name(John), Column B is their Spouse name(Jane) and Column C is the Last Name(Doe). When finished hoping that column D would say: John & Jane Doe

 

Thanks for your help!

excel
Formulas and Functions
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Dec 06, 2022

    BrokerJoey 

    Since I do not think any formula is complete until it outputs a dynamic array, one could exploit a weakness of the old CONCATENATE function, which, unlike CONCAT fails to concatenate the terms of an array,

     

    = CONCATENATE(
    Table1[First Name], " & ",
    Table1[Spouse Name], " ",
    Table1[Last Name]
  )

     

     

    Note: This formula only works when placed outside the Excel Table.  Inside the table, the "@" notation would be required and the table would propagate the formula down the column.

     

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Dec 06, 2022

    BrokerJoey There are couple of ways to do that. In simple you can use-

    =A2&" & " & B2 & " " &C2

    With CONCAT() function

    =CONCAT(A2," & ",B2," ",C2)

    For dynamic spill result with one formula. See the attached file.

    =BYROW(A2:C6,LAMBDA(x,CONCAT(CHOOSECOLS(x,1)," & ",CHOOSECOLS(x,2)," ",CHOOSECOLS(x,3))))

     

Resources