How to combine text in Excel (3 Easy Ways)
A free Office suite fully compatible with Microsoft Office
You can exhale with relief if you have a huge worksheet in an Excel workbook where you need to mix content from many cells since you don't have to retype it all. There are several simple ways to concatenate the text, some of which are listed below:
Combine text with An (&):
All versions of Excel on all operating systems are compatible.
The simplest method for merging text is to use a formula. The ampersand sign is used to connect cells together (&). The formula for joining the data in cells A2 and B2 is =A2&B2.
But to separate the first name from the last name in the output, we add another ampersand and enclose the space character in quote marks. The equation would be =A2& &B2
Formula to Combine Text with Ampersand
You could prefer that the last name appears first in the output, followed by a comma, and then the first name. If so, change your formula to change the order of the cells and include a comma before your space: =B2&, &A2
Combine text using the TEXTJOIN Function in a Formula:
What if there are three columns and only some of the cells have data? With another ampersand, you could add a new cell to your formula, but if a cell was empty, your output would also include a space character. Use a formula with the TEXTJOIN function to prevent this (available Excel versions 2019 and later).
Using the TEXTJOIN function, combine text.
There are three parameters for the TEXTJOIN function.
The delimiter argument comes first. This is the character (or string of characters) that should display in your cells in between the text. We want the names in our example to be separated by a space, so we enter .
The second justification is to ignore empty cells. Depending on whether you want Excel to ignore blank cells, you may select true or false. We shall enter TRUE because we do.
Text makes up the third argument. What you want to do is merge these cells. You have the option to choose just one of them or a range.
Our entire formula is expressed as =TEXTJOIN( ,TRUE,A2:C2)
Merge and center tool to combine text:
Another excellent method for swiftly combining many columns is using Merge and center tool in Excel.
You should choose the columns you wish to merge.Next, choose Merge and then centre in the home tab. By doing so, the Merge Columns Window will appear.Choose the method you choose for separating the text from each column.Hit OK.
Your data will be combined the way you want it to be combined if you use the above mentioned technique.
Did you learn about how to combine text in Excel? You can follow WPS Academy to learn more features of Word Document, Excel Spreadsheets and PowerPoint Slides.
You can also download WPS Office to edit the word documents, excel, PowerPoint for free of cost. Download now! And get an easy and enjoyable working experience.
- 1. How to get month name from a date in Excel (3 easy ways)
- 2. Check if value is in list in Excel (3 easy methods)
- 3. How to Copy File Names in Excel from a Folder?
- 4. How to add text to beginning or end of all cells in Excel
- 5. How to color cell based on value in Excel?
- 6. How to compare two excel sheets and highlight differences