How to combine two columns in Excel

Combining two columns in Excel into a single column is a common task that can be achieved in various ways depending on your specific needs. Here are some methods you can use:

Method 1: Using the Ampersand (&) Operator

1. Click on the cell where you want the combined data to start. This will be the first cell of your new column.
2. If you want to combine Column A and Column B, and you’re starting in Row 1, type =A1 & B1. You can also add a separator if needed, like a space, comma, or hyphen. For example, =A1 & ” ” & B1 will add a space between the two values.
3. Drag the fill handle (a small square at the bottom-right corner of the cell) down to fill the cells below with the combined data from the two columns.

Method 2: Using the CONCATENATE Function

1. Create a New Column for the combined data.
2. Type =CONCATENATE(A1, B1) for a simple combination without any separator. To add a separator, like a space, use =CONCATENATE(A1, ” “, B1).
3. Copy the Formula Down to apply it to the entire column.

Method 3: Using the TEXTJOIN Function

1. Create a New Column.
2. Enter the TEXTJOIN Formula. This function is useful if you want to add a consistent separator and ignore empty cells. Use =TEXTJOIN(” “, TRUE, A1, B1). The first argument (” “) is the separator, the second (TRUE) tells Excel to ignore empty cells, and the next arguments are the cells to join.
3. Copy the Formula Down.

Method 4: Using Flash Fill

1. Manually Enter the Combined Data in the first cell of the new column. For example, type the contents of A1 and B1 the way you want them combined.
2. Select the Cell with the data you just entered.
3. Go to the Data Tab and click on Flash Fill (or just press Ctrl + E). Excel will automatically fill the rest of the cells based on the pattern you provided.

Additional Tips

• If you want to convert your formulas to static values, copy the combined column and then paste it as values (Right-click – Paste Special – Values).
• Combining data can sometimes lead to data truncation (if the combined data is too long for a cell). Make sure to check the combined data for completeness.

Choose the method that best suits your needs based on the version of Excel you’re using and the specific requirements of your task (like whether you need separators or need to skip blank cells).