How To Combine 2 Columns in Excel With a Space

Did you know you can combine columns in Excel with a space?

Whether joining first and last names or merging data from different columns, doing this efficiently can save you time. Excel makes it easy to combine columns with just a simple formula. In this guide, you'll learn exactly how to do it step by step.

Combining Two Columns Using the CONCATENATE Function

You can quickly combine values in multiple cells using the CONCATENATE function. Follow these steps to use it:

Step 1: Open your Excel workbook.

Open the Excel file where you have your data or create a new one. Enter or find the data in two columns that you want to combine. For example, "First Name" is in column A, and "Last Name" is in column B.

Step 2: Select the cell for the combined result.

Click on the cell where you want the combined result to appear in a new column (e.g., cell C2). You can also name the column. For our example, we have named column C "Full Name."

Step 3: Enter the CONCATENATE formula.

In the selected cell, start typing the formula: =CONCATENATE(. Click on the first cell you want to combine (e.g., A2, which contains the first name).

After selecting A2, type a comma , and then a space enclosed in quotation marks " " (to add a space between the names). Type another comma , and then click on the second cell you want to combine (e.g., B2, which contains the last name). Close the formula with a parenthesis ).

Your formula should look like this: =CONCATENATE(A2, " ", B2)

Step 4: Press Enter to See the Result

Press the Enter key to complete the formula. You will see the combined text from the two columns with a space between them (e.g., "John Doe").

Step 5: Copy the formula down the column.

Click on the bottom right corner of the cell with the formula (it will look like a small square). Drag the formula down to fill the cells below, or double-click the small square to automatically fill the cells in the column.

Read more: How to swap columns in Excel.

Combining Two Columns Using the Ampersand (&) Operator

The ampersand (&) operator is a quick method to combine columns with a space. It is ideal for simple text concatenation tasks in Excel.

Step 1: Select the cell for the combined data.

Choose an empty cell where the result will appear.

Step 2: Enter the formula with the ampersand operator.

In the selected cell, start typing the formula using the = sign. Select the first cell to combine (e.g., A2, for the first name). Type an ampersand & to concatenate the text.

Next, type a space enclosed in quotation marks " " to add a space between the two values. Type another ampersand & to continue the concatenation. Select the second cell to combine (e.g., B2, for the last name).

The complete formula should look like this: =A2 & " " & B2

Step 3: Execute the formula.

Hit Enter to merge the texts from both columns, separated by a space.

Step 4: Apply the formula to other cells.

You have now combined two cells into a single cell. Use the fill handle to drag the formula down or double-click to replicate it for the remaining cells.

Read more: How to Remove Page Breaks in Excel.

Combining Two Columns Using the TEXTJOIN Function

The TEXTJOIN function is a versatile method that is available in Excel 2016 and later versions. TEXTJOIN is particularly helpful if you have a large dataset or want to handle multiple cells efficiently.

Step 1: Prepare your data.

Ensure you have the data you want to combine in two separate columns. For instance, "First Name" in column A and "Last Name" in column B.

Step 2: Choose the output cell.

Select the cell for the result.

Step 3: Write the TEXTJOIN formula.

Type = to start the formula. Enter TEXTJOIN( and open a parenthesis. The first argument is the delimiter. Type " " (quotation marks with a space inside) to specify a space between the combined texts.

After that, set the ignore_empty argument to TRUE by typing , TRUE,. This ensures that any empty cells are ignored in the merging process. Now, click on the first cell you want to combine (A2), add a comma, and click on the second cell (B2).

Your formula should look like this: =TEXTJOIN(" ", TRUE, A2, B2)

Step 4: Finalize the formula and check the result.

Press Enter. You should see the text from the two columns combined in cell C2, with a space in between.

Step 5: Extend the formula down the column.

Drag the fill handle (small square at the bottom right of the cell) down to apply the formula to other rows. You can also double-click the fill handle to quickly fill the rest of the column. Review the merged data and make necessary adjustments.

Combining Two Columns With a Space Using Flash Fill

Flash Fill is an Excel feature that automatically recognizes patterns and fills in data based on your examples. It’s perfect for quickly combining text from multiple columns with minimal effort. Here’s how you can use Flash Fill:

Step 1: Provide an example of the combined text.

In the first cell of the column where you want the combined result (let’s say cell C1), manually type the combined result from the two columns, including a space. For instance, if A2 is "John" and B2 is "Doe," you would type John Doe in C1.

Step 2: Start the Flash Fill.

After typing the example, press Enter to move to the next cell (C2). Begin typing the next combined entry, or you can just press Ctrl + E (the shortcut for Flash Fill), and Excel will automatically recognize the pattern based on your first entry.

Step 4: Review the automatically filled data.

Excel will fill down the rest of the column based on the detected pattern. Check that all entries are correctly combined with a space in between.

Final Thoughts on "How To Combine 2 Columns in Excel With a Space"

Combining two columns in Excel with a space is easy, whether you use a formula, the ampersand operator, TEXTJOIN, or Flash Fill. Each method has its benefits, allowing you to choose the one that best fits your needs. With these simple steps, you can quickly create a clean and organized dataset in no time.

You can visit our homepage for more easy-to-follow how-to and step-by-step guides. Check the links in related articles for further details about Excel/Google Sheets Templates!

Frequently Asked Questions

1. How do I combine two columns with a space in Excel?
You can use the CONCATENATE function, the ampersand (&) operator, or TEXTJOIN to merge the columns with a space. Flash Fill is another quick option that automatically combines data based on patterns.

2. What version of Excel is needed for the TEXTJOIN function?
The TEXTJOIN function is available in Excel 2016 and later versions. Make sure your Excel is up to date to use this feature.

3. Can I combine columns without losing data?
Yes, all methods listed will combine columns without deleting any original data. The combined result will appear in a new column, leaving your existing columns untouched.

Related Articles

How to Calculate Percentage Change in Excel

How to Alternate Row Colors in Excel

How to Use Excel for Budgeting