Combine Text in Excel - A Step-by-Step Guide

Introduction:

Microsoft Excel is a powerful tool that offers many features for manipulating and analyzing data. A common task is to merge or combine text from different cells into one cell. Whether you're merging first and last names or merging address elements, Excel provides several ways to accomplish this. In this blog, I'll show you various techniques for stitching together text in Excel so that you can streamline your data management process.

 

Method 1: Concatenate function

The CONCATENATE function is an easy way to combine text in Excel. To use it, follow these steps:

Step 1: Identifies the target cell to merge the text.

Step 2: Enter the following formula in the desired cell:

=CONCATENATE(cell1, cell2, cell3, ...)

Replace "cell1", "cell2", and "cell3" with the cell references containing the text you want to merge. You can add as many cell references as you need, separated by commas.

Step 3: Press Enter to display the merged text in the target cell.

Example 1: Using the CONCATENATE function

Suppose you have the following data in cells A1 and B1

A1: Roman

B1: Reigns

To combine first and last names in a single cell, do the following:

Step 1: Select the target cell (e.g. C1) where you want to paste the connected text.

Step 2: Enter the following formula in cell C1:

=CONCATENATE(A1, " ", B1)

This formula concatenates the text in cells A1 and B1 with a space between them.

Step 3: Press Enter to display the merged text in cell C1. The result looks like this:

Roman Reigns

 

Method 2: Ampersand Operator

In Excel, you can also use the ampersand (&) operator to join text strings. Steps to use this method: 

Step 1: Select the target cell where you want to place the merged text.

Step 2: Enter the following formula in the desired cell:

= cell 1 & cell 2 & cell 3 & ... Replace "cell1", "cell2", and "cell3" with the cell references containing the text you want to merge. As with the CONCATENATE function, separate cell references with the ampersand (&) operator.

Step 3: Pressing Enter displays the merged text in the target cell.

Example 2: Using the ampersand (&) operator

Consider another example with the following data in cells A1, B1, and C1:

A1: Hello

B1: Spider

C1: Man

To merge three cells into one cell, do the following:

Step 1: Select a target cell (such as D1).

Step 2: Enter the following formula in cell D1:

=A1 & " " & B1 & C1

This formula uses the ampersand operator to concatenate the text in cells A1, B1, and C1 with a space between them.

Step 3: Press Enter to display the bound text in cell D1. The result looks like this:

Hello SpiderMan 

Method 3: Text Join feature (Excel 2019 and newer)

Beginning with Excel 2019, Excel have introduced a new function called TEXTJOIN that makes joining text even more convenient. To use this feature, follow these steps:

Step 1: Determines the target cell to merge text.

Step 2: Enter the following formula in the desired cell: 

=TEXTJOIN(delimiter, ignore empty, cell 1, cell 2, cell 3, ...)

Replace "delimiter" with the character you want to use to separate the combined text. For example, use ',' to separate text with commas and spaces.

The ignore_empty (TRUE/FALSE) argument specifies whether to skip empty cells during merging. Replace "cell1", "cell2", and "cell3" with the cell references containing the text you want to merge, separated by commas.

Step 3: Press Enter to display the merged text in the destination cell. 

Example 3: Using the TEXTJOIN function

Suppose you have the following data in cells A1, B1, and C1: 

A1: Steve Roger

B1: is

C1: Captain America

To concatenate the fruit names in cells A1, B1, and C1 with a comma and a space, do the following:

Step 1: Select a target cell (such as D1).

Step 2: Enter the following formula in cell D3:

=TEXTJOIN(" ", TRUE, A1:C1)

This formula uses the TEXTJOIN function and specifies commas and spaces as delimiters. Specifying a TRUE value for the ignore_empty argument ensures that empty cells are skipped.

Step 3: Press Enter to display the bound text in cell D1. The result looks like this:

Steve Roger is Captain America

You can insert (,) instead of spaces by using (",") as the delimiter, as shown in the following example.

 

Conclusion:

Excel provides several methods for combining text, making it easy to combine and consolidate data. Whether you prefer features like CONCATENATE, the ampersand operator, or the new TEXTJOIN function, you can choose the method that suits your needs and preferences. Mastering these techniques will save you time and improve your data management capabilities in Microsoft Excel. 

                                           Click Here to Download latest MS Office