Combining two columns in Excel is a common task, especially when you need to merge first and last names, join address parts, or create unique IDs from separate pieces of information. This guide shows you the simplest ways to merge your data without losing the original information. We'll cover everything from quick, formula-free methods for beginners to more powerful functions for complex tasks, ensuring you can clean up your spreadsheets efficiently.

Fast Answer

  • Key Method: Use the Ampersand (&) formula.
  • Example Formula: =A2&" "&B2
  • What it does: This formula joins the text from cell A2, a space, and the text from cell B2 into a single cell.
2-5 Minutes Time needed
Beginner Difficulty
Losing formatting Watch out for

Before You Start

  • Microsoft Excel: This guide applies to all modern versions of Excel, including Microsoft 365, Excel 2021, 2019, and 2016.
  • Your Data: Have a spreadsheet ready with at least two columns of text or numbers you wish to combine.
  • An Empty Column: You will need a blank column next to your data where the combined results will go. This ensures your original data remains safe.
Check first: Always work on a copy of your important spreadsheets. A small mistake in a formula or an accidental drag-and-drop can overwrite your original data. Creating a quick backup gives you a safety net.

Step-by-Step Instructions

Step 1: Prepare Your Worksheet

Before you start, make sure your data is organised. Let's say you have first names in Column A and last names in Column B. You want to combine them into a "Full Name" column in Column C. Make sure Column C is completely empty, as this is where your results will appear.

For our example, cell A2 contains "Sarah" and cell B2 contains "Jenkins". We will put our formula in cell C2.

Step 2: Start the Combination Formula

The simplest way to join text in Excel is by using the ampersand (&) symbol. It acts like glue, sticking text strings together.

Click on the first cell in your empty destination column (C2 in our example). Type an equals sign (=) to tell Excel you are starting a formula. After the equals sign, click on the first cell you want to combine (A2). Your formula bar should now show:

=A2

Now, type an ampersand (&) directly after it. The formula will look like this:

=A2&

Step 3: Add a Space or Separator

If you just combine A2 and B2 directly (=A2&B2), the result will be "SarahJenkins" with no space. To add a space, you need to tell Excel to treat it as a piece of text. Any literal text in a formula must be wrapped in double quotation marks.

After the first ampersand, type " " (a double quote, a space, then another double quote). This tells Excel to insert a space. Your formula should now be:

=A2&" "

Next, you need to add another ampersand to "glue" the next piece of data on. Type another &. The formula is now ready for the last part:

=A2&" "&

Tip: The separator can be anything you want. To join names with a comma and a space, you would use ", " instead of just " ". For a hyphen, you'd use "-".

Step 4: Add the Second Column and Complete the Formula

Now, add the final piece of data. After the second ampersand, click on the cell containing the last name (B2). Your formula is now complete:

=A2&" "&B2

Press the Enter key on your keyboard. Cell C2 will immediately display the combined result: "Sarah Jenkins". You have successfully combined your first two cells.

Step 5: Apply the Formula to the Entire Column

You don't have to retype the formula for every single row. Excel has a clever feature called the "Fill Handle" that does this for you.

Click on cell C2, where you just entered your formula. Look at the bottom-right corner of the cell's green border. You will see a tiny, solid green square. This is the Fill Handle.

Move your mouse cursor over this square. Your cursor will change from a thick white cross to a thin black cross. When it does, double-click it.

Excel will instantly copy the formula down to every row that has adjacent data in columns A and B. Your entire "Full Name" column is now populated.

Step 6: Convert Formulas to Permanent Values (Important!)

Your new column C currently contains formulas, not text. If you were to delete Column A or B, Column C would show an error because its source data is gone. To make the combined data permanent, you need to replace the formulas with their results (values).

  1. Select your new data. Click the column header (the letter "C") to select the entire column.
  2. Copy the data. Right-click on the selection and choose Copy, or press Ctrl+C.
  3. Paste as Values. Right-click on the selected column again. This time, look for the Paste Special options. Choose Values. The icon usually looks like a clipboard with "123" on it.

The formulas in Column C are now gone, replaced by the actual text "Sarah Jenkins", "David Lee", etc. You can now safely delete the original first and last name columns if you no longer need them.

Check first: Be absolutely sure you have the combined data correct before deleting the original columns. This step cannot be easily undone after saving the file.

Alternative 1: Using the CONCAT or CONCATENATE Function

Excel has a dedicated function for joining text, which can be easier to read if you're combining many items. In the newest versions of Excel, this function is called CONCAT. In older versions (Excel 2016 and earlier), it is called CONCATENATE.

To use it, click on cell C2 and type the following formula:

=CONCAT(A2, " ", B2)

Each piece of text you want to join is separated by a comma. Just like with the ampersand method, we include " " to add a space. Press Enter and use the Fill Handle to apply it to the rest of the column. Both methods achieve the same result.

Alternative 2: Using Flash Fill (The No-Formula Method)

For simple combinations, Excel can often figure out what you want to do without any formulas at all. This feature is called Flash Fill.

  1. In cell C2, manually type out the exact result you want. For our example, you would type "Sarah Jenkins" and press Enter.
  2. Now, click on the cell below it (C3) and start typing the result for the next row. For example, if A3 is "David" and B3 is "Lee", start typing "David".
  3. As you type, Excel should detect the pattern from the row above and show a greyed-out preview of the rest of the column filled in for you.
  4. If the preview is correct, simply press the Enter key to accept it. The entire column will be filled instantly.

If the preview doesn't appear, you can trigger it manually. After typing your first example in C2, select cell C3, go to the Data tab on the Excel ribbon, and click the Flash Fill button.

Tip: Flash Fill is incredibly fast for one-off tasks. However, unlike a formula, it will not automatically update if you change the original data in columns A or B.

Quick Reference

Situation Use This Method Why It's a Good Choice
You need full control and custom separators (like commas or hyphens). Ampersand (&) Formula It's flexible, works in all Excel versions, and is easy to read for simple combinations like =A2&"-"&B2.
You are combining many columns in a row and want a cleaner formula. CONCAT Function The formula =CONCAT(A2," ",B2," ",C2) can be tidier than a long chain of ampersands.
You need a quick, formula-free solution for a one-time task. Flash Fill (Ctrl+E) Excel does the work automatically by detecting your pattern. It's the fastest method but doesn't update if source data changes.
You need to join text with line breaks (e.g., for an address block). TEXTJOIN Function This advanced function lets you specify a delimiter (like a line break) to use between each item. Example: =TEXTJOIN(CHAR(10), TRUE, A2:B2).

Common Problems When You Combine Two Columns In Excel

Problem: The combined text has no space, like "JohnSmith".

Cause: This is the most common issue and happens when you forget to add a space separator in your formula. A formula like =A2&B2 directly joins the cell contents with nothing in between.

Solution: Edit your formula to include a space enclosed in double quotes. The correct formula is =A2&" "&B2. Remember to use the Fill Handle to re-apply the corrected formula to the whole column.

Problem: Dates or numbers combine incorrectly.

Cause: Excel stores dates and times as serial numbers. For example, the date 1st May 2026 is stored as the number 46142. When you combine this with text, you'll see the number, not the formatted date.

Solution: Use the TEXT function to format the number or date as text before you combine it. The formula is =TEXT(cell, "format"). For a date in A2, you would use: =TEXT(A2, "dd/mm/yyyy") & " - " & B2. This converts the date into a "01/05/2026" text string before joining it.

Problem: Flash Fill doesn't work or gets the pattern wrong.

Cause: Flash Fill needs a very clear and consistent pattern in your source data. If you have extra spaces, inconsistent capitalisation, or empty cells, it can get confused.

Solution: First, clean up your source data to be as consistent as possible. Second, give Flash Fill more than one example. Manually type the correct combined text for the first two or three rows. This often gives Excel enough information to find the correct pattern. If it still fails, it's more reliable to use a formula with the Ampersand or CONCAT.

Problem: A #VALUE! error appears in the cell.

Cause: This error usually means you've used the wrong operator. If you accidentally use a plus sign (+) instead of an ampersand (&), Excel will try to perform a mathematical calculation. It cannot "add" text, so it returns an error.

Solution: Check your formula carefully. Ensure you are only using the & symbol or a text function like CONCAT to join your columns. Replace any + signs that are meant for joining text with &.

Advanced Tips for Combining Columns In Excel

Combining with Line Breaks

Sometimes you need to combine text into a single cell but on multiple lines, like for a mailing address. You can do this by using Excel's character code for a line break, which is CHAR(10).

The formula would be: =A2 & CHAR(10) & B2. After entering this formula and copying it down, you must select the column and turn on Wrap Text (found on the Home tab of the ribbon). This will make the line breaks visible within the cells.

Using the Powerful TEXTJOIN Function

Modern Excel versions have a superior function called TEXTJOIN. It is especially useful because it can handle a range of cells and automatically ignore any empty ones.

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

  • Delimiter: The character to put between each item (e.g., ", ").
  • Ignore_empty: Set to TRUE to skip blank cells.
  • Text1, text2...: The cells or range you want to join.

For example, =TEXTJOIN(" ", TRUE, A2:C2) will join everything from A2 to C2 with a space, automatically skipping any cell that is blank. This is much cleaner than complex, nested IF formulas.

Creating Custom IDs or Email Addresses

You can combine columns with other functions to generate useful data. For example, to create a standard company email address from a first name in A2 and a last name in B2 (e.g., s.jenkins@digioptimal.co.uk), you can nest other functions inside:

=LOWER(LEFT(A2,1)) & "." & LOWER(B2) & "@digioptimal.co.uk"

This formula takes the first letter of the first name (LEFT(A2,1)), converts it to lowercase (LOWER), adds a full stop, adds the lowercase last name, and appends the company domain.

How To Combine Two Columns In Excel FAQ

How do I combine two columns in Excel without losing the original data?

The safest method is to always perform the combination in a new, empty column. This leaves your original columns (the source data) completely untouched. Once you have confirmed the combined column is 100% correct, you can use the "Paste Special > Values" command to make the results permanent, and only then should you consider deleting the original columns if they are no longer needed.

Can I combine more than two columns?

Yes, absolutely. With the ampersand (&) method, you can chain together as many cells as you need. For example: =A2&" "&B2&", "&C2. The CONCAT and TEXTJOIN functions also allow you to include multiple cells or even a continuous range of cells, such as =CONCAT(A2:D2).

How do I combine columns but keep the formatting for numbers or currency?

When you combine cells, the result is always a text string, which loses any special number formatting (like currency symbols or comma separators). To preserve it, you must use the TEXT function. For example, to combine text in A2 with a currency value in B2, your formula should be: =A2 & ": " & TEXT(B2, "£#,##0.00"). This formats the number from B2 as pounds with two decimal places before joining it.

Is there a keyboard shortcut to combine columns in Excel?

While there isn't a direct shortcut for a combination formula, the fastest way to combine columns without typing a formula is to use Flash Fill. The keyboard shortcut for Flash Fill is Ctrl+E. Just type your first desired result manually, press Enter, then move to the cell below and press Ctrl+E. Excel will attempt to fill the rest of the column based on your example.

Final Checklist for Combining Two Columns In Excel

  • Backup Created: Have you saved a copy of your file before starting?
  • Method Chosen: Did you choose the right tool for the job? (Flash Fill for speed, Ampersand for control, or CONCAT/TEXTJOIN for power).
  • Separator Included: Did you remember to add a separator like " " or ", " so your text is readable?
  • Formula Applied to All Rows: Have you used the Fill Handle to quickly copy your formula down the entire column?
  • Formulas Converted to Values: If you plan to remove the source data, have you used "Paste Special > Values" to make your results permanent?
  • Formatting Checked: If combining numbers or dates, have you used the TEXT function to ensure they appear correctly?