How to merge two columns of data in excel

Answer

To merge two columns of data in Excel, you have several methods depending on whether you want to keep the original columns or combine them into a new column.

Method 1: Using the CONCATENATE Function or CONCAT Function

1. Select the cell where you want the merged result to appear.

2. Enter the formula:

   • For Excel versions before 2016: 

     =CONCATENATE(A1, B1)

   • For Excel 2016 and later: 

     =CONCAT(A1, B1)

3. Press Enter.

4. Drag the fill handle down to apply the formula to other rows.

Variation with a Separator:
To add a space, comma, or other separator between values: 

=CONCAT(A1, " ", B1)

This merges the content of column A and column B with a space in between.

Method 2: Using the "&" Operator

1. Select the cell for the merged output.

2. Enter the formula: 

   =A1 & B1

3. Press Enter.

4. Drag the formula down for all rows.

With Separator: 

=A1 & " " & B1

This inserts a space between the merged values.

Method 3: Using Flash Fill

1. Type the desired merged value manually in the first row of a new column. For example, if A1 is "John" and B1 is "Doe", type "John Doe".

2. Press Enter.

3. Select the next cell below, start typing the merged format, or press Ctrl + E (Windows) / Command + E (Mac).

4. Excel automatically fills the rest of the column with merged values based on the pattern.

Method 4: Using Power Query (For Large Data Sets)

1. Select your data and go to Data → Get & Transform → From Table/Range.

2. In Power Query Editor, select the columns you want to merge.

3. Right-click and choose Merge Columns.

4. Choose a separator (space, comma, etc.) or no separator.

5. Click Close & Load to return merged data to Excel.

Tips

• Formulas keep the original columns intact. Copy and paste values as text to remove formulas.

• For very large datasets, Flash Fill is faster, but Power Query is more robust for automated merging.

• Ensure there are no leading or trailing spaces in cells, as these may affect the final result.

Yes · 0 No · 0 Add Comment · 0

Add ·

Report