Sometimes while using Excel we feel the need to combine columns containing text. There are some quick and easy methods to combine texts from two columns in Excel. We are going to see some methods like CONCATENATE, TEXTJOIN functions, and some other methods using Symbols.
To get a better understanding, we are going to use a sample dataset as an example. The following dataset represents First and Last Names.
Download Practice Workbook
6 Quick and Easy Methods to Combine Texts from Two Columns in Excel
Method 1: Combining Excel Columns with Symbols
In this method, we are going to use Ampersand (&) to combine columns containing text in Excel. We can add dash (_), comma (,), hyphen (–), and space ( ) in between the texts.
1.1 Combine Columns Containing Texts with a Dash
We want to combine columns containing text and want a Dash between them, for example, we want to combine cells B5 and C5. Let’s see how we will do it,
First, we will select cell D5, because we want our data to show here, after that, we will type the following formula.
Press ENTER key, and we will get the following result.
After pressing enter the result will be Larry_Bird. So, here we are combining cell B5 and cell C5 and using Dash (_) as a separator.
Now we can click and drag down to AutoFill the formula for the rest of the cells.
1.2 Combine Columns Containing Texts with a Comma
What if we want to add Comma instead of Dash in between our texts.
Suppose, we want to combine texts in cell B5 and cell C5 and want our combined data in cell D5. So, here are the steps,
First, we will select cell D5, as we want our data to show here. After that, we will type the following formula
Here, to combine the values of cells B5 and C5 we used ampersand (&).
Now press the ENTER key, This will display the cell as Larry, Bird.
Now we can right-click the mouse button and drag it down to AutoFill the formula for the rest of the cells.
Finally, we get our desired results.
1.3 Combine Two Columns Containing Texts with a Hyphen
This time we want to combine columns containing text and also want a Hyphen as a separator. Let’s see, how to do it in Excel,
For instance, we want to combine cell B5 and C5, and want our data in cell D5.
Therefore, at first, we will click cell D5.
After that, we will type the following formula.
Now press the ENTER key and will get the following result.
Here we are telling Excel to combine cells B5 and C5 and as we want a Hyphen (–) between the texts, we simply typed “-” in the formula to get a hyphen as a separator, otherwise, if we ignored it and typed =B5&C5 we will get LarryBird as a result.
Now no need to do other cells manually, cause we will use AutoFill. Simply drag down clicking the Right Key on the mouse. And we are done.
1.4 Combine Columns Containing Texts with a Space
Here we will see how to combine columns containing text in Excel and use Space as a separator. We want to add cells B5 & C5 and want a space between the added data, let’s see how to do it.
First click the cell D5, because, we want our combined data here,
Now we will type the following formula.
Press ENTER key.
We got our result Larry Bird. Here we combined cell B5 and C5 with an ampersand, but note that we used space in quotation marks “ ”. This command is telling Excel to add Space between that two texts. Now we will use AutoFill. We can simply click the Right Button on the mouse and drag it down. The result will be as follows.
Read More: How to Insert Column without Affecting Formulas in Excel (2 Ways)
Method 2: Add Up Additional Texts in Combined Cells
Sometimes we not only want to combine texts from two columns in Excel but also want to add some additional text. For Example, we have two names in Cell B5 and C5, rather than just adding these two cells we want our combined cell to show a full sentence. Let us see how we can do this.
First, click on cell E5.
Now we will type the formula as follows.
="The person"&" "&B5&" "&C5&" "&"is a basketball player"
Press ENTER key.
So we get the result: The person Larry Bird is a basketball player. What’s happening here. We are telling Excel that we want the text The person in the very first position of the combined data, Therefore, we typed =” The person”. After that, we typed & “” & B5 & “” & C5 & “”&” is a basketball player” This formula added space after the text, combined the cell data from B5 and C5, and also combined it with the text is a basketball player.
After this, we can simply click the Right button on the mouse and drag it down to get our results.
So here we go, we didn’t need to type formulas manually for each cell. That’s the magic of AutoFill.
Note: We can also combine text data from columns B and C in Column D. For Example we can simply use the following formula to combine cells B5 And C5 with additional text easily
="The person"&D5&" "&"is a basketball player"
Read More: Insert Column with Name in Excel VBA (5 Examples)
Method 3: Displaying Numbers in Combined Cells
Sometimes the columns we want to combine may contain Formatted Numbers like Currency, Date, Time, Fraction, Percentage, etc. In this case, to avoid striping in combined cells we can use the TEXT function formula.
If we click in Cell E5, we can see that in the upper right section of the date currency is displaying. This is to say, we have to combine columns containing both text data and currency data.
That’s why, we have to tell Excel how we want our data, otherwise, it will return just the text and plain numbers for the formatted cells. Let’s see how it works
Click on cell F5.
Now we will type
=D5&" Has got "&TEXT(E5,"$##.00")
Now we will press ENTER key.
So, we got the result Larry Bird Has got $20.00. One thing we have to keep in mind is that cell D5 is a combined cell. We can use different ways to combine cells. Just check the methods above if you need them.
To clarify, Here we are combining Cell D5 (Larry Bird) with the text Has got and the formatted number $20.00. By typing TEXT(E5,”$##.00″) we are telling Excel to convert the currency format with text format because we want to show the data in two digits after the decimal.
Now we will click the Right Button on the mouse and drag down to AutoFill the rest of the series.
Read More: How to Insert Column with Excel VBA (4 Ways)
Method 4: Using CONCATENATE Function to Combine Texts from Two Columns
In this method, we will see how CONCATENATE functions work. We have the datasheet containing First Name and Last Name, let’s see how we can combine texts from these two cells in a single cell.
Select cell D5, as we want our combined data here.
Now we have to type the following formula
Now press the Enter key. The D5 cell will look like this.
What happened here is Excel added cells B5 and C5 and included Space as a separator. To include the space we typed blank space “ ” in the formula.
Now we right-click the mouse button and drag it down to the rest of the cell. This is an AutoFill feature in Excel and this helps you to save time.
Note: You can also use the CONCAT function instead of CONCATENATE function.
Read More: Shortcuts to Insert Column in Excel (4 Easiest Ways)
Method 5: Using TEXTJOIN Function to Combine Texts from Two Columns
With the introduction of Excel 2016, the TEXTJOIN function was added. However, this formula is not available in earlier versions of Excel, such as 2013, 2010, or 2007, for example.
Click the cell where we want the combined data to go.
We want to add Columns B5 and C5 in a single column D5.
First click in cell D5
Now type the function as follows:
Now press the ENTER key.
First, we assigned “,” as Delimiter, which means we want a comma in between our text, then we typed, TRUE (to ignore_empty cells).
Finally, we typed comma (,) and selected the cells B5 and C5 as we want to join these two cells. Excel is returning us the result Larry, Bird.
Note: As a separator, you can choose a comma, dash, hyphen, etc. according to our convenience. You just need to command the intended symbol when typing the formula. Like
=TEXTJOIN("-",TRUE,B5:C5) if we want Hyphen
=TEXTJOIN("_",TRUE,B5:C5) if we want Hyphen
Now we can right-click the mouse and drag it down to the rest of the cell. It will AutoFill all other cells according to the formula we used.
Read More: How to insert column in Excel (Quick 5 methods)
Method 6: Merge Columns in Excel
We can combine texts from two columns in Excel by merging them but it will keep the values from the upper-left cell and discard the rest.
If we want to merge cell B5 and cell C5, the merged cell will only show the data from the upper-left cell B5. Let’s see how it works.
First, select both the cells B5 and C5
After that go to the Home tab and select Merge and Centre from the ribbon.
Now the cell will look like this in the following image. It has discarded the text Bird and kept only the upper left part, Larry.
Note: While joining these two cells after clicking Merge & Centre a dialogue box will pop up, we have to click OK here.
Read More: How to Insert a Column to the Left in Excel (6 Methods)
Things to remember
While to combine columns that contain formatted numbers, like Date format we will use the formula:
=D5&" was born on "&TEXT(E2,"dd/mm/yyyy")
These are 6 different techniques to combine texts from two columns in Excel. You can select the best method based on your preferences. Please let me know if you have any questions or feedback in the comments section. You may also go through our other Excel-related articles on this site.