How to Combine Text From Multiple Columns in Excel

Excel does not have a single "merge columns" button that combines data the way it combines cells

When you merge cells in Excel, you are joining the visual space — the cells disappear into one box. When you merge columns of data, you are combining the actual text or numbers from separate columns into a single column. These are two different operations, and the second one is what most people need.

The most straightforward method is the concatenation formula, which joins text from multiple cells into one. You write a formula that says "take what is in column A, add what is in column B, put the result in column C." Excel then copies that formula down for every row. This keeps your original data intact and lets you undo the operation if needed.

A second option is the Find & Replace method, which works when your columns are already separated by a consistent character — like a space or comma — and you want to remove that separator. This is faster if your data is already structured that way, but it permanently changes your data unless you undo it when ready.

Key Takeaways

The concatenation formula (using the ampersand symbol & or the CONCATENATE function) combines text from multiple cells without erasing your original data.
You write the formula once in a new column, then copy it down to explore it to every row at once.
If your columns are separated by a space or other character, Find & Replace can remove the separator, but this permanently changes your data.
Always work in a new column first so you can see the result before deleting the original columns.

Using the Ampersand Formula to Combine Columns

Open your spreadsheet and click on an empty column where you want the combined data to appear — usually the column right after your last data column. Click on the first cell in that column (for example, if your data starts in row 2, click the cell in row 2 of your new column).

Type this formula: =A2&" "&B2 (replace A2 and B2 with the actual column letters and row numbers of the cells you want to combine). The quotation marks with a space between them add a space between the combined text. If you do not want a space, leave the quotation marks empty: =A2&B2. Press Enter.

Excel shows the combined result in that cell. Now click on the cell again to select it, then look for the small square in the bottom-right corner of the cell (called the fill handle). Click and drag that square down to the last row of your data. Excel copies the formula to every row and adjusts the row numbers automatically — so row 3 becomes =A3&B3, row 4 becomes =A4&B4, and so on.

Once the formula is applied to all rows, you can delete your original columns if you no longer need them. Click on the column letter at the top to select the entire column, right-click, and choose Delete.

Using CONCATENATE When You Have Many Columns

If you are combining more than two columns, the ampersand method still works but becomes harder to read. The CONCATENATE function does the same thing in a clearer way.

Click on your empty cell and type: =CONCATENATE(A2," ",B2," ",C2). List each cell you want to combine, separated by commas. The quotation marks with spaces add spaces between each piece of text. Press Enter, then drag the fill handle down to copy the formula to all rows.

The result is identical to the ampersand method — CONCATENATE is just easier to understand when you are combining four or more columns at once. Some versions of Excel also have a TEXTJOIN function, which works similarly but gives you more control over what goes between the pieces of text.

Removing Separators With Find & Replace

If your columns are already separated by a consistent character — a space, comma, dash, or other symbol — and you want to remove that separator, Find & Replace is faster than writing a formula.

First, select all the data you want to change. Click on the column letter of your first data column and drag to the last column, or click the first cell, hold Shift, and click the last cell. Then press Ctrl+H (or Cmd+H on Mac) to open Find & Replace.

In the "Find what" field, type the character you want to remove — for example, a space or comma. Leave the "Replace with" field empty. Click "Replace All." Excel removes every instance of that character in your selection. This permanently changes your data, so if you make a mistake, press Ctrl+Z when ready to undo.

This method works best when your columns are already separated by something you do not need. If you want to keep the original data or add something different between the columns, use the formula method instead.

Combining Columns While Keeping Formatting

If your cells contain numbers formatted as currency, dates, or percentages, the concatenation formula treats them as text. A cell with $50.00 becomes the text "50" in your combined column, losing the dollar sign and decimal places.

To preserve formatting, wrap each cell in the TEXT function. Instead of =A2&" "&B2, write =TEXT(A2,"$0.00")&" "&TEXT(B2,"0%"). The format codes in quotation marks tell Excel how to display each number. "$0.00" shows currency with two decimal places; "0%" shows a percentage. You can adjust these codes to match your data — look up "Excel TEXT function format codes" if you need a different format.

This approach takes more time to set up but preserves the way your numbers look in the original data.

Turning Your Formula Into Permanent Data

After you have combined your columns with a formula, the new column still contains formulas, not actual text. If you delete the original columns, your combined column shows errors because the formulas no longer have anything to reference.

To make the combined data permanent, select the column with your formulas, copy it (Ctrl+C), then right-click and choose "Paste Special." Click the "Values" option and click OK. Excel replaces the formulas with the actual text they produced. Now you can safely delete the original columns.

Common Mistakes and How to Avoid Them

The most common error is forgetting to add spaces or other separators between the pieces of text. If you write =A2&B2 without the space in the middle, "John" and "Smith" become "JohnSmith" instead of "John Smith." Always include &" "& between each cell reference if you want spaces.

Another mistake is deleting the original columns before converting your formulas to values. If you delete column A while column C still contains =A2&B2, column C shows an error. Always convert to values first, then delete the original columns.

A third issue is selecting the wrong range when using Find & Replace. If you do not select your data first and just press Ctrl+H, Find & Replace affects your entire spreadsheet, including column headers and other text you did not intend to change. Always select only the cells you want to modify before opening Find & Replace.

Frequently Asked Questions

Can I merge columns without creating a new column?

Not directly. You can write a formula in a new column, then copy the results and paste them back into one of your original columns as values. But this requires extra steps. It is simpler to keep the combined data in a new column, then delete the original columns once you have verified the result is correct.

What if my columns contain different data types, like text and numbers?

The ampersand formula and CONCATENATE both treat everything as text in the result. A number like 50 becomes the text "50" with no formatting. If you need to preserve number formatting (currency, percentages, dates), use the TEXT function to format each cell before combining them.

How do I add line breaks between combined text?

Use CHAR(10) instead of a space. Write =A2&CHAR(10)&B2. Then right-click the cell, choose Format Cells, go to the Alignment tab, and check "Wrap text." The combined text now appears on separate lines within the same cell.

Can I undo a Find & Replace that removed my separators?

Yes, when ready press Ctrl+Z after you realize the mistake. This undoes the last action. If you have already done other work since the Find & Replace, undo will reverse all of it. This is why the formula method is safer — you can always delete the formula column and start over without affecting your original data.

What is the difference between CONCATENATE and the ampersand method?

They produce identical results. The ampersand method (=A2&B2) is shorter to type. CONCATENATE (=CONCATENATE(A2,B2)) is clearer to read, especially with many columns. Use whichever feels more natural to you — both work the same way.

This guide is for general information only and is not medical, financial, legal, or other professional advice. For decisions specific to your situation, consult a qualified professional. See our Editorial Policy.