Best Ways to Combine Two Columns in Excel Without Losing Data

Data management often requires merging separate pieces of information into a single, cohesive cell. Whether you are dealing with first and last names, mailing addresses, or product codes and descriptions, knowing how to combine two columns in Excel efficiently is a fundamental skill. While many users instinctively reach for the "Merge & Center" button, this is a common mistake that leads to data loss. In Excel, merging cells physically combines them but only retains the data from the upper-left cell, discarding everything else.

To preserve your information and create dynamic datasets, you need functional methods that concatenate the strings. This guide explores several professional techniques—ranging from simple operators to advanced AI-driven features—to help you merge data while maintaining its integrity.

Using the Ampersand (&) symbol for quick merges

The ampersand symbol is the most straightforward method for joining text in Excel. It functions as a text operator, essentially telling Excel to "take this and add that." This method is preferred by many because it is fast, does not require memorizing complex function names, and is highly flexible.

How to apply the Ampersand method

To combine data from cells A2 and B2 into cell C2, the basic syntax is =A2&B2. However, simply joining them often results in the text running together (e.g., "JohnDoe"). To add a space, comma, or other separator, you must enclose the separator in double quotation marks.

1. Click the cell where you want the combined result to appear (e.g., C2).
2. Type the equals sign = to start the formula.
3. Click the first cell (A2).
4. Type & followed by a space enclosed in quotes " ".
5. Type another & and click the second cell (B2).
6. The final formula looks like this: =A2&" "&B2.

Why use the Ampersand?

This method is perfect for simple tasks. Its main advantage is that it allows for "nesting" other types of data easily. For instance, if you want to add a specific phrase between the two columns, you could use =A2 & " works in " & B2. The ampersand handles different data types—like joining a text string with a number—without much fuss.

Leveraging CONCAT and CONCATENATE functions

For users who prefer using formal functions over operators, Excel offers CONCATENATE and its modern successor, CONCAT. While CONCATENATE is available for backward compatibility with older versions of Excel, CONCAT is the standard for 2026 users, offering better range support.

The difference between CONCAT and CONCATENATE

The older CONCATENATE function requires you to select every cell individually: =CONCATENATE(A2, " ", B2, " ", C2). This becomes tedious if you are merging more than two columns. The newer CONCAT function, introduced in Office 2019 and refined in Microsoft 365, allows you to specify a range, such as =CONCAT(A2:D2), although it still doesn't automatically add delimiters like spaces.

Steps to use CONCAT

1. Select your target cell.
2. Enter =CONCAT(A2, " ", B2).
3. Press Enter.
4. Use the fill handle (the small square at the bottom-right of the cell) to drag the formula down for the entire column.

While functional, CONCAT is often eclipsed by a much more powerful function that handles the "separator problem" more elegantly.

The Professional Choice: TEXTJOIN

If you are working with Excel 2019 or later, TEXTJOIN is arguably the best formulaic way to combine two or more columns. It addresses the two biggest headaches of data merging: delimiters and empty cells.

The power of delimiters and ignoring empties

When using the ampersand or CONCAT, if one of your columns is empty, you might end up with extra spaces or trailing commas. TEXTJOIN solves this with a specific argument to ignore empty cells.

The syntax is: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...).

How to use TEXTJOIN

Imagine you are combining First Name, Middle Name, and Last Name. Not everyone has a middle name. Using a standard formula would leave a double space for those without a middle name. With TEXTJOIN:

1. Type =TEXTJOIN(" ", TRUE, A2, B2, C2).
2. The first argument (" ") defines a space as the separator.
3. The second argument (TRUE) tells Excel to skip any cell that is blank.
4. The remaining arguments are the cells or ranges you want to merge.

This creates a clean, professional result regardless of whether some cells in your columns are empty. It is particularly useful for building mailing addresses where "Suite Number" or "Region" might be missing for certain entries.

Using Flash Fill for non-formula results

Flash Fill is a revolutionary feature that uses pattern recognition to merge columns without any formulas at all. This is ideal for users who find formulas intimidating or for those who need an immediate, static result that won't change if the source columns are deleted.

How to trigger Flash Fill

Flash Fill works by "watching" what you do and mimicking the pattern.

1. In the column next to your data, manually type exactly how you want the first row to look. For example, if A2 is "Jane" and B2 is "Smith", type "Jane Smith" in C2.
2. Move to the next cell (C3) and start typing the result for the second row.
3. Excel will usually show a grayed-out list of suggestions for the rest of the column.
4. Press Enter to accept the suggestions.

Alternatively, you can type the first entry and press Ctrl + E on your keyboard to instantly fill the entire column.

Limitations of Flash Fill

While fast, Flash Fill is not dynamic. If you change "Jane" to "Janet" in column A, the combined value in column C will not update. Use Flash Fill for one-time cleaning tasks, but stick to formulas if your data is subject to frequent changes.

Merging large datasets with Power Query

For users handling thousands of rows or those who need to repeat the same merging process every week with new data imports, Power Query is the most robust solution. Power Query is Excel’s built-in data transformation engine, and it handles column merging with a dedicated user interface.

Steps to merge in Power Query

1. Select your data range and go to the Data tab.
2. Click From Table/Range (this will open the Power Query Editor).
3. In the editor, hold the Ctrl key and click the headers of the two columns you want to combine.
4. Right-click on one of the selected headers and choose Merge Columns.
5. In the dialog box that appears, select a separator (like a space, comma, or semicolon) and give your new column a name.
6. Click OK.
7. Click Close & Load to return the merged data to a new sheet in Excel.

Power Query is "refreshable." If you add new rows to your original table, you can simply right-click the Power Query result table and hit "Refresh" to merge the new data automatically.

Crucial Step: Converting formulas to values

A common pitfall when combining columns is deleting the original source columns immediately after writing a formula. Because the formula in Column C depends on Columns A and B, deleting A and B will result in a #REF! error in your merged column.

To prevent this, you must convert the formulas to static values once you are satisfied with the result:

1. Highlight all the combined cells in your new column.
2. Press Ctrl + C to copy them.
3. Right-click on the same selection.
4. Under "Paste Options," select the Values icon (it looks like a clipboard with the numbers '123').

Now, the text in those cells is independent of the original columns, allowing you to delete the source data safely.

Handling complex data types: Dates and Currency

One of the most frustrating experiences in Excel is merging a column of names with a column of dates. If A2 is "Hired on: " and B2 contains the date "2026-05-20", a standard formula like =A2&B2 will yield something like "Hired on: 46162". This is because Excel stores dates as serial numbers.

Using the TEXT function for formatting

To keep the date looking like a date, you must use the TEXT function within your merge formula. The TEXT function allows you to apply a format to a number or date before it gets combined into the string.

The Formula: =A2 & TEXT(B2, "mm/dd/yyyy")

This tells Excel to take the date in B2 and convert it to a recognizable text string before joining it with A2. You can use similar logic for currency: =A2 & " " & TEXT(B2, "$#,##0.00").

Comparison of methods

Troubleshooting common errors

Why is there no space between my words?

If your result is "NewYork" instead of "New York," you likely forgot to add the space string " " between your ampersands or function arguments. Remember that Excel treats everything inside quotes as literal text.

Why does my formula show as text and not the result?

If you see =A2&" "&B2 inside the cell instead of the combined name, the cell is likely formatted as "Text" instead of "General." Change the cell format to "General" and double-click the cell to re-enter the formula.

How do I add a line break between columns?

If you want the second column to appear on a new line within the same cell, use the CHAR function. For Windows users, CHAR(10) creates a line break. Formula: =A2 & CHAR(10) & B2. Note: You must turn on "Wrap Text" in the Home tab to see the line break effect.

Conclusion and best practices

Efficiency in Excel is about choosing the right tool for the specific job. For a quick name merge, the ampersand is unbeatable. For complex datasets with missing values, TEXTJOIN is the gold standard. For heavy-duty data preparation, Power Query provides the most control and scalability.

Always remember to back up your original data before performing major transformations, and if you intend to delete your source columns, never forget to paste your results as values. By mastering these diverse techniques, you can ensure your spreadsheets remain clean, professional, and error-free.