How To Combine Two Columns In Excel

Exceloffice workflowhow-to

Combining two columns in Excel is a common task for cleaning up data, like joining first and last names into a single full name column, or merging product codes with descriptions. This guide helps when you have data spread across multiple columns and need to consolidate it into one for better organisation, reporting, or exporting to another system. We will walk you through the easiest formula-based methods, a clever no-formula trick, and how to make your new data permanent and safe from future errors.

Fast Answer

Easiest Formula: In a new cell, type =A2 & " " & B2 to combine the text from cell A2 and B2 with a space in between.
No-Formula Method: Use the Flash Fill feature on the Data tab after typing one example manually.

5 minutes Time needed

Beginner Difficulty

Losing original data Watch out for

Before You Start

Before you begin combining data, a little preparation ensures the process is smooth and your original information remains safe. This isn't a destructive process if you follow one simple rule.

What You Need

Microsoft Excel: Any version from Excel 2013 onwards will have all the features mentioned, including Flash Fill. The core formulas work in any version.
Your Data Sheet: Have your spreadsheet open with the two (or more) columns you want to combine clearly identified.
An Empty Column: You must have a spare, empty column to the right of your data. This is where the combined text will go.

Safety and Context Checks

Data Integrity: Check for hidden leading or trailing spaces in your source columns. These can cause formatting issues. Use the `=TRIM()` function to clean them up first if needed.
Data Type: Be aware that combining columns containing numbers or dates can sometimes produce unexpected results. We cover how to handle this in the common problems section.
Backups: While this process is safe, working on a copy of your file is always a good habit, especially with critical business data.

Check first: Never try to combine columns by overwriting one of the original columns. Always create your formulas in a new, separate column. This protects your original first names, last names, or other data pieces from being accidentally deleted.

Step-by-Step Instructions

We'll cover the most practical and popular methods, starting with the universally recommended Ampersand (&) formula, which is quick and easy to remember.

Prepare Your New Column

The first and most important step is to create a dedicated space for your combined data. Find the first empty column to the right of the columns you wish to merge. For example, if your first names are in column A and last names are in column B, you'll work in column C.

Click on the header of this new column (e.g., the letter 'C') and give it a descriptive name, like "Full Name" or "Combined ID". This keeps your spreadsheet organised and easy for others (and your future self) to understand.

Use the Ampersand (&) Formula

The ampersand symbol (&) is Excel's operator for joining, or "concatenating," text. It is the most direct way to combine columns. Let's assume your first name is in cell A2 and your last name is in B2.

Click on the first empty cell in your new column (in our example, this would be C2).
Type the following formula directly into the cell or the formula bar above: =A2&" "&B2
Press Enter. The cell C2 should now display the full name, like "John Smith".

Let's break down that formula: The first equals sign (=) tells Excel you're starting a formula. A2 refers to the first cell. The first & joins it to the next part. " " is how you tell Excel to insert a literal space character; anything inside double quotes is treated as plain text. The second & joins the space to the content of cell B2.

Tip: You can put anything inside the quotes. To separate names with a comma and a space, your formula would be =B2&", "&A2 to get a result like "Smith, John".

Apply the Formula to the Entire Column

Now that you have the formula working for the first row, you don't need to type it out for every single entry. You can use Excel's "Fill Handle" to copy it down instantly.

Select the cell containing your new formula (C2). Look for the small, solid green square at the bottom-right corner of the cell border. This is the Fill Handle. Hover your mouse over it until the cursor changes to a thin, black cross (+). Now, you can either:

Double-click the Fill Handle. Excel will automatically copy the formula down to the last row of your adjacent data. This is the fastest method for large datasets.
Click and drag the Fill Handle down the column manually to the last row of data you want to combine.

Excel is smart enough to update the cell references automatically. The formula in C3 will become `=A3&" "&B3`, the one in C4 will be `=A4&" "&B4`, and so on.

Try the Flash Fill Method (No Formulas)

If formulas aren't your thing, Excel has a powerful pattern-recognition tool called Flash Fill. It's perfect for simple combination tasks.

Make sure you have an empty column (e.g., C) right next to the columns you're combining (A and B). This proximity is important for Flash Fill to work.
In the first cell of the empty column (C2), manually type out the exact result you want. If A2 is "John" and B2 is "Smith", you would type John Smith into C2.
Press Enter to move to the cell below (C3).
Start typing the next name in the pattern. As soon as you type the first letter of the second entry (e.g., "J" for "Jane Doe"), Excel should show a greyed-out preview of all the combined names for the rest of your list.
If the preview looks correct, just press Enter to accept it. The entire column will be filled instantly.

If the preview doesn't appear automatically, you can trigger it manually. After typing your first example in C2, go to the Data tab on the Excel ribbon and click the Flash Fill button (it looks like a spreadsheet with a small lightning bolt).

Use the CONCATENATE Function (Alternative Formula)

Before the ampersand (&) became the standard, the `CONCATENATE` function was the primary tool for this job. It works identically and you may see it in older spreadsheets. It is still fully functional.

In your new column (cell C2), you would type: =CONCATENATE(A2, " ", B2)

Here, the function `CONCATENATE()` takes a list of items to join, separated by commas. `A2` is the first item, `" "` is the second item (the space), and `B2` is the third. The result is exactly the same as the ampersand method. You can then use the Fill Handle to apply this formula to the rest of your column as described in Step 3.

Convert Your Formulas to Static Values

This is a crucial final step. Your new "Full Name" column is currently powered by formulas. If you were to delete the original "First Name" or "Last Name" columns, your combined column would break and show errors. To make the data permanent, you need to convert the formulas into their resulting text values.

Highlight the entire new column that contains your combined names (click the column letter, 'C', at the top).
Copy the data. You can do this by right-clicking and selecting Copy, or by pressing Ctrl + C (Cmd + C on Mac).
With the column still highlighted, right-click on it again. In the context menu, look for Paste Options and select the icon that looks like a clipboard with "123" on it. This is Paste Values.

You will not see any change in the data itself, but if you click on any cell in that column, the formula bar will now show the actual text (e.g., "John Smith") instead of the formula (`=A2&" "&B2`). Your data is now independent and safe. You can now delete the original columns if you no longer need them.

Quick Reference

Situation	Use this	Why
Joining 2-3 columns simply	Ampersand (&) formula	It's the quickest, most modern, and easiest formula to read for simple merges.
You prefer not to use formulas	Flash Fill	It's an automatic, pattern-based tool that requires no formula writing at all.
Working with older spreadsheets	CONCATENATE function	It's the classic function for this job and ensures compatibility with very old Excel files.
Combining many columns with a consistent separator	TEXTJOIN function	This advanced function is ideal for joining a range of cells with a single delimiter and can ignore empty cells.

Common Problems When You Combine Two Columns In Excel

Sometimes things don't go exactly as planned. Here are a few common issues and how to fix them quickly.

Problem: The combined text has no space between words (e.g., "JohnSmith").
Fix: You forgot to add the space separator in your formula. Your formula probably looks like `=A2&B2`. Edit it to include `" "` between the ampersands: =A2&" "&B2.
Problem: Flash Fill isn't working or gives the wrong result.
Fix: Flash Fill needs a clear pattern and works best when the new column is directly adjacent to the source columns. Check for inconsistent data or extra spaces in your source cells. Sometimes, providing a second or third manually typed example can help Excel understand a more complex pattern.
Problem: The cell shows an error like #NAME? or #VALUE!.
Fix: A #NAME? error usually means you've misspelled a function name (e.g., `CONCAT` instead of `CONCATENATE`). A #VALUE! error can happen if part of your formula is invalid. Double-check your spelling and ensure all cell references are correct.
Problem: Dates or numbers combine in a strange format.
Fix: Excel stores dates as serial numbers (e.g., 44197 instead of 01/01/2021). When you combine a cell with a date, you combine the number, not the formatted date. To fix this, use the `TEXT` function to format the date before joining. Example: =A2&" - Hired on "&TEXT(B2, "dd mmmm yyyy"). This would result in "John Smith - Hired on 01 January 2021".

Advanced Tips for Combining Columns in Excel

Once you've mastered the basics, you can use more powerful techniques for complex situations.

Using the TEXTJOIN Function: This is a superior version of CONCATENATE, available in Excel 2019 and Microsoft 365. Its main advantage is handling delimiters and empty cells. The syntax is `=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)`
Example: To join cells A2, B2, and C2 with a comma and space, but only if the cell isn't empty, you'd use =TEXTJOIN(", ", TRUE, A2:C2). This is much cleaner than a long ampersand formula.
Adding Line Breaks: You can combine text onto multiple lines within a single cell. Use the `CHAR(10)` function to insert a line break.
Example: =A2&CHAR(10)&B2. After entering this formula, you must select the cell(s) and enable "Wrap Text" from the Home tab for the line break to be visible.
Automating with Power Query: For repetitive tasks where you frequently import and combine data, use Power Query (Get & Transform Data). You can load your data, select the columns to merge, and specify a separator. This creates a repeatable process that updates automatically when your source data changes, saving huge amounts of time.

How To Combine Two Columns In Excel FAQ

Can I combine more than two columns in Excel?

Yes. Simply extend the formula. Using the ampersand method, you would write =A2&" "&B2&" "&C2 and so on for as many columns as you need.

How do I combine columns but keep the originals?

The methods described in this guide are non-destructive by design. You always perform the combination in a new, empty column, which leaves your original columns untouched and completely safe.

What's the absolute fastest way to combine two columns?

For a one-off task, Flash Fill is often the fastest as it involves no formula typing. For a task you might repeat, setting up an ampersand (&) formula and double-clicking the fill handle is extremely fast for thousands of rows.

How do I add a different separator, like a hyphen?

Simply change what is inside the double quotes in your formula. To use a hyphen surrounded by spaces, your formula would be =A2&" - "&B2.

Why should I convert my formulas to values at the end?

Converting to values makes your new data independent. If you leave the formulas and later delete or change the original source columns (e.g., "First Name"), your combined column will break. Pasting as values locks in the result permanently.

Final Checklist for Combining Two Columns In Excel

New Column Created: You have added a new, empty column for the combined results.
Correct Formula Used: Your formula (e.g., `=A2&" "&B2`) correctly references the first row of data.
Separator Included: You have included a separator like `" "` or `", "` inside your formula to ensure readability.
Formula Applied to All Rows: You have used the fill handle (double-click or drag) to copy the formula down the entire dataset.
Results Checked: You have scanned the results to ensure they look correct and there are no formatting surprises.
Converted to Values: You have used Copy and Paste Special > Values on the new column to make the data permanent and safe from future errors.

Reader note

When a page mentions a commercial resource, Hotgingerweb tries to keep the decision criteria visible beside the link. The practical fit matters more than the promotion.