how to combine 2 columns in excel with a space

2025

2 min read

·

21-03-2025

15

0

Share

Combining two columns in Excel with a space between the data in each row is a common task for data cleaning and preparation. This guide will show you several methods, from simple formulas to more advanced techniques, ensuring you find the best solution for your needs. Whether you're working with names, addresses, or any other paired data, we'll cover how to efficiently merge your columns.

Method 1: Using the CONCATENATE Function (Excel 2019 and earlier)

The CONCATENATE function is a straightforward way to join text strings. This is a good option if you are using an older version of Excel.

Steps:

1. Select the cell where you want the combined data to appear.
2. Enter the formula: =CONCATENATE(A1," ",B1) (Replace A1 and B1 with the cells containing the data you want to combine. The " " adds a space between the values.)
3. Press Enter.
4. Drag the fill handle (the small square at the bottom right of the cell) down to apply the formula to all the rows.

Example: If cell A1 contains "John" and cell B1 contains "Doe," the formula will return "John Doe" in the selected cell.

Method 2: Using the & Operator (Excel 2019 and earlier)

This method offers a more concise way to achieve the same result as CONCATENATE. The & operator joins text strings together.

Steps:

1. Select the cell where you want the combined data to appear.
2. Enter the formula: =A1&" "&B1 (Again, replace A1 and B1 with your cell references).
3. Press Enter.
4. Drag the fill handle down to apply the formula to all rows.

Example: This formula achieves the exact same result as the CONCATENATE example above: "John Doe".

Method 3: Using the TEXTJOIN Function (Excel 2019 and later)

The TEXTJOIN function provides more flexibility, especially when combining more than two columns.

Steps:

1. Select the cell where you want the combined data.
2. Enter the formula: =TEXTJOIN(" ",TRUE,A1,B1) (The " " specifies a space as the delimiter. TRUE ignores empty cells).
3. Press Enter.
4. Drag the fill handle to apply to all rows.

Example: This also results in "John Doe," but is more adaptable for multiple columns.

Method 4: Combining Columns Directly (Without Formulas)

For a permanent change, you can combine the columns directly without relying on formulas. Note that this method overwrites the original data, so it's crucial to back up your spreadsheet before proceeding.

Steps:

1. Select the first empty column to the right of your data.
2. Type the formula from any of the previous methods into the first cell of that column.
3. Copy that cell and paste it down for all relevant rows.
4. Once finished, copy the newly created column with the combined data.
5. Paste Special -> Values over the original column(s) you want to replace. This pastes only the values, not the formulas.
6. Delete the original columns.

Which Method Should You Choose?

• For simplicity and compatibility with older Excel versions: Use the CONCATENATE function or the & operator.
• For more flexibility and handling multiple columns: Use the TEXTJOIN function (Excel 2019 and later).
• For a permanent change without formulas: Use the "Combine Columns Directly" method, but remember to back up your data first!

Remember to always double-check your formulas and results to ensure accuracy. By following these methods, you can efficiently combine columns in Excel and prepare your data for further analysis or use.