To combine two columns in Excel, the most common and effective ways are to use formulas like CONCATENATE, TEXTJOIN, the CONCAT function, or the ampersand (&) operator. Here is a concise guide:
Using CONCATENATE or CONCAT formula
- Insert a new helper column next to the two columns you want to combine.
- In the first cell of the helper column, enter the formula to combine cells from two columns. For example:
- =CONCATENATE(A1, " ", B1) or =CONCAT(A1, " ", B1)
- This joins the contents of column A and B with a space in between.
- Drag the fill handle down the helper column to apply the formula to all rows.
- Copy the merged column and paste as values if you want to remove the formula.
Using the Ampersand (&) operator
- Insert a helper column next to your data columns.
- In the first cell of the helper column, enter the formula:
- =A1 & " " & B1
- This also combines the two columns' text with a space.
- Fill down the formula for all rows and convert to values as needed.
Using TEXTJOIN (Excel 2016 and later)
- If you want to combine multiple cells with delimiters while ignoring blanks, use:
- =TEXTJOIN(" ", TRUE, A1:B1)
- This joins the range A1:B1 with spaces, skipping empty cells.
These methods preserve the original data while creating a combined string in a new column, which can be converted to static text if required for further use.
