5 Easy Ways to Combine Text from Multiple Cells in Excel (Step-by-Step Guide)

How to Combine Text from Two or More Cells into One Cell in Excel

When working with large datasets in Excel, you might need to combine text from multiple cells into one cell. Whether you’re merging names, addresses, or other information, Excel makes it easy to join text using simple formulas. This guide will show you how to combine text from two or more cells into one using various methods.

Method 1: Using the Ampersand (&) Operator

The simplest way to combine text from multiple cells is by using the ampersand (&) operator.

Steps:

1. Select the cell where you want to combine the text.
2. Type the formula:
   =A1 & B1
   This will combine the text in cells A1 and B1.
3. If you want to add a space, comma, or any other separator between the text, modify the formula like this:
   =A1 & " " & B1
   This adds a space between the text in cells A1 and B1.

Example:

• Cell A1 contains John
• Cell B1 contains Doe

Formula: =A1 & " " & B1
Result: John Doe

Method 2: Using the CONCATENATE Function

Before Excel 2016, the CONCATENATE function was the go-to method for combining text. Though it's replaced by the TEXTJOIN and CONCAT functions in newer versions, it still works well in older Excel versions.

Steps:

1. Select the cell where you want to display the combined text.
2. Enter the formula:
   =CONCATENATE(A1, B1)
   This merges the content of cells A1 and B1.
3. To add a separator, include it in the formula:
   =CONCATENATE(A1, " ", B1)

Example:

• Cell A1 contains John
• Cell B1 contains Doe

Formula: =CONCATENATE(A1, " ", B1)
Result: John Doe

Method 3: Using the CONCAT Function (Excel 2016 and Later)

The CONCAT function is similar to CONCATENATE, but it offers more flexibility, especially in combining ranges.

Steps:

1. Select the cell where you want the combined text to appear.
2. Enter the formula:
   =CONCAT(A1, B1)
3. Add separators as needed:
   =CONCAT(A1, " ", B1)

Method 4: Using the TEXTJOIN Function (Excel 2016 and Later)

If you need to combine a range of cells and insert a delimiter (like a space, comma, or semicolon), the TEXTJOIN function is perfect.

Steps:

1. Select the cell where you want the result.
2. Enter the formula:
   =TEXTJOIN(" ", TRUE, A1, B1)
   This combines A1 and B1, with a space as a separator. The second argument (TRUE) tells Excel to ignore empty cells.
3. You can also combine entire ranges with this function:
   =TEXTJOIN(", ", TRUE, A1:C1)

Example:

• Cell A1 contains John
• Cell B1 contains Doe
• Cell C1 contains Smith

Formula: =TEXTJOIN(" ", TRUE, A1:C1)
Result: John Doe Smith

Method 5: Using Flash Fill (Excel 2013 and Later)

Flash Fill is an intelligent tool that detects patterns in your data and automatically fills cells. This method is especially useful when you have simple combinations or transformations.

Steps:

1. Type the combined version of the text in the first cell. For example, if A1 contains John and B1 contains Doe, type John Doe in the adjacent cell.
2. Press Enter, then start typing the next combined value.
3. Excel will offer to fill in the remaining cells with similar combinations automatically. If it does, press Enter to accept the suggestions.

Conclusion

Combining text in Excel is a common task, and whether you're using simple formulas like &, the CONCATENATE or TEXTJOIN functions, or Excel’s smart Flash Fill feature, you can easily merge text from two or more cells into one. The right method depends on the complexity of your data and the version of Excel you're using.

Now that you know several ways to combine text in Excel, you can choose the best one for your needs and streamline your data management tasks!