So earlier we had CONCAT, and CONCATENATE function to concatenate multiple cells. But if we wanted to supply a range for joining cells with a delimiter (say a comma) then it is really tricky with these functions. But now Excel has introduced a new function called TEXTJOIN Function that can be used to concatenate ranges with a lot more flexibility.
Here we will talk about how to combine cell contents in Excel easily and quickly. Merge And Combine Rows Without Losing Data In Excel Excel only keeps the data in the upper-left most cell, if you apply 'Merge & Center' command (Home tab Merge & Center on the Alignment panel) to merge rows of data in Excel.
In this article, we will learn how to concatenate cell values of a range with comma using TEXTJOIN function. For users who don't have this function we will discuss other methods of concatenating range values with comma.
How to combine multiple cells in excel without losing data? Easiest way to do this is by the use of ampersand, ie. =A&B If Cell 1=A and Cell 2=B, then the contents of both cells can be merged within Cell 3 by applying formula =Cell1&Cell2. Please refer to the following excel spreadsheet for example. For example, if you wanted to combine cell A2 and B2, the formula would be: =CONCAT(A2,B2) This formula used to be CONCATENATE, rather than CONCAT. While the former still works, it is being depreciated and you should use the latter to ensure compatibility with current and future versions of Excel. To combine text from multiple cells into one cell, use the & (ampersand) operator. Select the cell in which you want the combined data Type an = (equal sign) to start the formula Click on the first cell. Mar 04, 2021 To combine the contents of several cells, you select the range to concatenate and configure the following settings: Cells into one under ' What to merge '; Select the delimiter you want under ' Separate values with ', it's a comma and a space in this example; Choose where you want to place the.
Comma (',') : This is the delimiter we want to use. Since in this article we are concentrating on concatenating cells with commas.
TRUE : For ignoring blank cells in the range.
Text_range1 : This is the range whose cells have values you want to concatenate.
[Text_range2] : The other ranges if you want to join in the text with commas.
Let’s see an example to make things clear.
Example: Join Cell Values of Ranges With Comma as Delimiter
Here, we have some values in range B2:E8. We need to join the texts of each cell in a row.
Let's implement the formula we have and drag it down.
You can see that we have a string which is a result of concatenation of texts with commas.
Let's say if you want to concatenate the range B3:E3 and B7:E7. So the formula will be:
It will concatenate all the texts ignoring the blank cells.
How does it work?
The formula is simple. The TEXTJOIN function requires the delimiter with which you want to join text with. The second variable is set to be true so that it ignores the blank cells.
Now if any cell has invisible values like space then you will see an extra comma in between the joined text.
To avoid spaces, use the TRIM function to strip them out.
Concatenating Cells with Commas in Excel 2016 and Older
The problem is that the TEXTJOIN function is only available to Excel 2019 and 365. So if you want to concatenate the cells with commas, we'll need to use a trick.
So to concatenate cells in a row with commas do this.
In a cell, write '=' to start the formula and select the range as shown below.
Now press F2 and select the range in the formula bar or cell.
Press F9 key.
Now remove the equals and curly braces. You have the cells joined with commas.
But this way is not that effective for too many operations.
So do we have any other way to combine texts with a given delimiter in Excel? The other way is the VBA way. Let's create one UDF to do this.
Press CTRL+F11 to open the VB Editor. Right click on the workbook and insert a module. Copy the code above and paste in the module's code area.
Now use this formula to join text with any delimiter you want to.
This formula will work in any version of Excel. You can download the workbook below to use this formula immediately.
So yeah guys, this is how you can join text with comma delimiter in Excel. I hope it was helpful for you. If you have any questions regarding this topic or any other excel related topic, ask in the comments section below. Till then keep Excelling.
Split Numbers and Text from String in Excel 2016 and Older: When we didn't have TEXTJOIN function we used LEFT and RIGHT functions with some other functions to split numeric and nonnumeric characters from a string.
Extract Text From A String In Excel Using Excel's LEFT And RIGHT Function: To remove text in excel from string we can use excel's LEFT and RIGHT function. These functions help us chop strings dynamically.
Remove leading and trailing spaces from text in Excel: Leading and trailing spaces are hard to recognize visually and can mess up your data. Stripping these characters from the string is a basic and most important task in data cleaning. Here's how you can do it easily in Excel.
Remove Characters From Right: To remove characters from the right of a string in Excel, we use the LEFT function. Yes, the LEFT function. The LEFT function retains the given number of characters from LEFT and removes everything from its right.
Remove unwanted characters in Excel: To remove unwanted characters from a string in Excel, we use the SUBSTITUTE function. The SUBSTITUTE function replaces the given characters with another given character and produces a new altered string.
How to Remove Text in Excel Starting From a Position in Excel: To remove text from a starting position in a string, we use the REPLACE function of Excel. This function help us determine the starting position and number of characters to strip.
50 Excel Shortcuts to Increase Your Productivity Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
Download pycharm professional for free. How to use Excel VLOOKUP Function This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
Excel Combine Cell Contents Table
How to use theExcel COUNTIF Function Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel This is another dashboard essential function. This helps you sum up values on specific conditions.
How to display all contents of a cell in Excel?
In Excel, sometimes, the cell contents are too many to display fully in the cell as below screenshot shown. Here in this tutorial, it provides some ways to display all content in a cell for users in Excel.
In Excel, the Wrap Text function will keep the column width and adjust the row height to display all contents in each cell.
Select the cells that you want to display all contents, and click Home > Wrap Text.
Then the selected cells will be expanded to show all contents.
Display all contents with AutoFit Column Width function
If you do not want to change the row heights of cells, you can use the AutoFit Column Width function to adjust the column width of cells for showing all contents.
Select the cells that you use, and click Home > Format > AutoFit Column Width.
Then the cells will be adjusted the column width for displaying the cell contents.
If there are large of contents in cells which you do not want to change the row height and column width of cells for keeping good-look of the worksheet, you can view all contents by using Kutools for Excel’s Enhanced Edit Bar function, which can display all contents in a prompt dialog while you click at the cell.
|with more than 300 handy functions, makes your jobs more easier.|
After free installing Kutools for Excel, please do as below:
Click Kutools > Enhanced Edit Bar to enable the Enhanced Edit Bar.
From now on, while you click at a cell, a dialog will prompt to display all contents of the active cell, and also, you can edit contents in this dialog directly to update contents in the cell.
Excel Join Cells With Text
Other Operations (Articles) Related To Text
Convert date stored as text to date in Excel
Occasionally, when you copy or import dates from other data sources to Excel cell, the date might become formatted and stored as texts. And here I introduce the tricks to convert such these dates stored as texts to standard dates in Excel.
Add text cells together into one cell in Excel
Sometimes, you need to combine text cells together into one cell in some purpose. In this article, we will show you two methods to add text cells together into one cell in Excel with details.
Allow only numbers to be input in text box
In Excel, we can apply the Data Validation function to allow only numbers to be entered into cells, but, sometimes, I want only numbers to be typed into a textbox as well as in cells. How to accept only numbers in a textbox in Excel?
Change case of text in Excel
This article is going to talk about the methods you can apply for changing text case easily in Excel.
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range..
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average.. Prevent Duplicate Cells; Compare Ranges..
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select..
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more..
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails..
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments..
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic..
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF..
- Pivot Table Grouping by week number, day of week and more.. Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name..
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!