How to Concatenate Text in a Spreadsheet
June 10th, 2008
If you have text in two or more columns that you wish to join (concatenate) in another column, this can be easily done by creating a formula that utilizes the ampersand (&). I have found this to work in Microsoft Excel, OpenOffice’s Calc, and iWork’s Numbers.
For our example, we will use a scenario where a list of users’ first and last names are given to you and your boss wants them to be in a single column instead of two.
You could take the time to copy and paste the names together in a column, but there is a much easier way. We can go to C2 and input the formula: =A2&” “&B2 we would then get the concatenated string of Bob Smith in the new cell.
We could use =B2&”, “&A2 and produce Smith, Bob in the new cell.
Copy the formula to the rest of the cells in the column to concatenate the desired cells.