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.
- How To Combine Data From 2 Cells In Excel
- Add Text From Two Cells
- How To Combine Data From Two Cells In Excel
- Combine Text From Two Cells Into One
- Combine Two Cells In Excel 2016
- Merging Data From Multiple Cells In Excel
- You can combine data from multiple cells into a single cell using the Ampersand symbol (&) or the CONCAT function. Combine data with the Ampersand symbol (&) Select the cell where you want to put the combined data. Type = and select the first cell you want to combine.
- When you work with Excel, you probably have to deal with data separated into multiple rows. For example name, city, age, etc. But sometimes you may want to combine cells from two different columns into a single column – for example, you have the First Name and Last Name columns and you want to merge them into a single Name.
- 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. Users have to use another method to merge multiple rows of data into one row without deleting data. Combine Duplicate Rows And Sum The Values In Excel.
- 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.
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.
Here's my entire playlist of Excel tutorials: Learn how to easily combine information from multiple cells in Excel and then put the.
Generic Formula
=TEXTJOIN(',',TRUE,text_range1,[text_range2]..) |
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.
=TEXTJOIN(',',TRUE,B3:E3) |
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:
=TEXTJOIN(',',TRUE,B3:E3,B7:E7) |
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.
=TEXTJOIN(',',TRUE,TRIM(B3:E3,B7:E7)) |
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.
How To Combine Data From 2 Cells In Excel
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.
Related Articles:
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.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
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.
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.
Summary:
Does merging rows and columns in Excel seems a tough task for you to perform? Read this tutorial to learn different ways to merge rows and columns in Excel.
Microsoft Excel is a very useful application and can be used for performing various tasks. This is the reason Excel provides various useful functions to make the task easy for the users.
One of the most common tasks that everyone needs performing now and then is merging rows and columns.
But the problem is that performing this is not an easy task and Excel does not provide any tool to do this.
This is quite complicated as merging rows and columns in some cases causes data loss.
As while trying to combine two or more rows in the worksheet by making use of the Merge & Center button (Home tab > Alignment group), you will start getting the error message:
“The selection contains multiple data values. Merging into one cell will keep the upper-left most data only.”
And if you click OK, merged cells would contain just the value of the top-left cell and as a result, entire other data will be removed.
So this is what leads you to Panic situation!!!
To get rid of this, today in this article I am sharing different ways to easily merge rows and columns in excel without losing any data.
Below check out the fixes on how to merge rows in Excel or how to merge columns in Excel.
![Combine Data From Two Cells In Excel Combine Data From Two Cells In Excel](/uploads/1/3/7/5/137531291/467858380.png)
To recover Excel data without any data loss, we recommend this tool:
This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:
A list of Discords for Realm of the Mad God, a unique free to play roguelike permadeath bullet hell MMORPG. 36,781 members. RotMG is a free-to-play permadeath bullet-hell MMORPG. Battle alongside dozens of players through the Mad God's Realm! 50,545 members. Rotmg discord.
- Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
- Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
- Preview the repaired files and click Save File to save the files at desired location.
How To Merge Rows & Columns In Excel Without Losing Data?
There are different methods for combining row and columns text in Excel. Here check the ways one by one to merge data without losing it. First, check how to merge rows in Excel.
Part 1# How To Merge Rows in Excel
When it comes to merging the Excel rows there are two ways that allow you to merge rows data easily.
- Merge Excel rows using a formula
- Combine multiple rows using the Merge Cells add-in
1. How to Merge Multiple Rows using Excel Formulas
Excel provides various formulas that help you combine data from different rows. Possibly the easiest one is the CONCATENATE function. So here checks out some examples for concatenating numerous rows into one:
Greek sigil. Hecate Goddess Wiccan Spells Green Witchcraft Sigil Magic Sacred Plant Eclectic Witch Baby Witch Witch Spell Triple Goddess Keeping Her Keys - Hekate, witchcraft and everyday life. Symbol of rebirth and renewal. The Hecate wheel is a symbol of Hecate who is an ancient Greek goddess who rules over the earth, sea, and sky. She is a protective goddess who bestows prosperity and daily blessings to families. Hekate sigil/ symbol idea. Gods And Goddesses. Hekate (also spelled as Hecate) is the Greek goddess of witchcraft, ghosts, magic, the moon, necromancy and crossroads. She is commonly associated as a Crone goddess, but is. Hekate's Sigil Pendant, 925 Silver Corypheon 5 out of 5 stars (277) $ 61.43 FREE shipping Add to Favorites Occult pillar candles YalevkaMoon 5 out of 5 stars (9) $ 9.99. Only 1 available and it's in 7 people's carts. Add to Favorites Wheel of Hecate Crystal Necklace, Hekate Labyrinth Pendant, lapis lazuli charm necklace, goddess hecate jewelry.
- Merge rows with spaces between data: For example =CONCATENATE(B1,” “,B2,” “,B3)
- Combine rows without any space between the values: For example =CONCATENATE(A1,A2,A3)
- Merge rows > separate the values with comma: For Example =CONCATENATE(A1,”, “,A2,”, “,A3)
Now check how the CONCATENATE formula works on the real data.
- On the sheet choose an empty cell and type the formula into it. Type the formula as per the data rows
- And copy the formula across entire other cells in the row.
- Now, simply you are having several data rows merged into one row.
2. How to Combine Rows in Excel using the Merge Cells Add-in
The Merge Cells add-in is used for merging various types of cells in Excel. This allows you to merges the individual cells and also combines data from entire rows or columns.
Please Note: You need to download a merge cell add-ins for third-party sites available online. Search in Google for add-ins.
Follow the given steps to combine two or more rows in your table:
- Choose rows you are looking to merge > click on the Merge Cells icon.
- Now the merge cells dialog window opens with a table or range selected already. And in the upper part of the window, you can see the three basic things:
- How you want to join cells– For combining rows of data > choose “column by column“.
- How to separate merged values with – an array of standard separators is available to choose from > comma, space, semicolon, anda line break. So select the separator as per your desire.
- Where you need to place the merged cells > either the top cell or bottom cell.
- Now check the lower part of the Windows to check if you need any additional options:
- Clear the content of selected cells – Choose this if need data to remain in the merged cells only.
- Merge all areas in the selection – This option allows you to merge rows in two or more non-adjacent ranges.
- Skip empty cells and Wrap text – Well, these are self-explanatory.
- Lastly, Create a backup copy of the worksheet – This option is checked by default. It is just a precaution that keeps you on the safe side and prevents the risk of data loss.
- Click the Merge button > to check the result – possible the merged rows of data separated by line breaks.
So, these are the two ways that allow you to merge rows in Excel without any data loss. Now, check out the ways on how to combine two columns in Excel.
Part 2# How To Merge Columns In Excel
Here check out the 3 ways to merge data from several columns into one without using VBA macro.
- Merge two columns using formulas
- Combine columns data via NotePad
- The fastest way to join multiple columns
1. Merge Two Columns using Excel Formulas
1. Into your table > insert a new column > in the column header place the mouse pointer > right-click the mouse > select Insert from the context menu. Name the newly added columns for eg. – “Full Name”
2. In the cell D2, write the formula: =CONCATENATE(B2,” “,C2). The B2 and C2 are the addresses of First Name and Last Name. And in the formula, the quotation marks “” is the separator that will be inserted between merged names any other symbol can be used as a separator e.g. a comma.
3. Just like this, join data from several cells into one by making use of any separator of your choice.
4. Simply, copy the formula to other cells of the Full Name column. If the First name or the Last name is deleted, then the corresponding data in the Full name Column will also be gone.
5. Next, try converting the formula to a value so that you can remove the unnecessary columns from the Excel worksheet. Choose entire cells with data in the merged column (choose the first cell in “Full Name” Column > press Ctrl +Shift + Arrow Down)
6. Now copy the contents of the columns to clipboard > right click on the cell in the same column (“Full Name”) > choose “Paste Special” context menu > choose “Values” radio button > click OK.
7. Now remove “First Name” & “Last Name” columns that are not required. Click the column B header > press and hold Ctrl > click column C header.
8. After that make a right-click on any selected columns > select Delete from the context menu.
9. This is it, now you have successfully merged the names from 2 columns into one.
2. Combine columns Data via Notepad
This is another way that allows you to merge several columns. Here you don’t need any formulas. This is suitable for combining adjacent columns to make use of the same delimiter for all of them.
For Example: If looking for combining 2 columns with First Names and Last Names into one:
- Choose both columns you need to merge: Click B1 > press Shift + ArrrowRight for choosing C1 > then hit Ctrl + Shift + ArrowDown for choosing entire data cells with data in two columns.
- And copy data to clipboard > open Notepad > insert data from the clipboard to the Notepad
- Then copy tab character to clipboard > hit Tab right in Notepad > hit Ctrl + Shift + LeftArrow > press Ctrl + X.
- After that Replace Tab characters in Notepad with the separator, you require.
- Hit Ctrl + H for opening the “Replace” dialog box > paste the Tab character from the clipboard in Find what field > type the separator Space, comma etc in “Replace with” field. Hit the Replace All button > to close the dialog box press Cancel
- Now select the entire text in the Notepad and copy it to Clipboard.
- Then switch back to Excel worksheet (press Alt + Tab) > choose B1 cell and paste text from Clipboard to your table.
- And rename column B to “Full Name“ and remove the “Last name” column.
So, this is the second way that allows you to merge columns in Excel without any data loss.
3. Join Columns Using Merge Cells Add-in For Excel
This is the easiest and quickest way for combining data from numerous Excel columns into one. Just make use of the third party merge cells add-in for Excel.
And with the merge cells add-in you can merge data from many cells by using any separator you like (for example carriage return or line break). With this, you can join row by row, column by column, or merge data from the selected cell into one without any loss.
There are many third-party add-ins online sites that allow you to download the add-ins and merge the cells easily in just a few clicks.
Conclusion:
So this is all about merging rows and columns in Excel without any data loss.
Follow the given steps to combine text in rows and columns easily.
Hope the given different steps will allow you to perform the task easily in the rows and column. Here I have described different methods of merging rows and columns data in Excel without any data loss.
So make use of anyone that you find easy for you.
However if in case you come to face any issue or data loss situation in Excel then make use of the MS Excel Repair Tool. This is the best tool that allows you to repair and recover data from the corrupted, damaged Excel file.
Additionally, you can learn advanced Excel to become more productive and easily utilize Excel functions and formulas.
Add Text From Two Cells
5 Easy Methods To Merge Rows and Columns in Excel Without Losing Data
Description
How To Combine Data From Two Cells In Excel
Merging Excel rows and column is a very important task but if facing problem then read this article to rows and columns in Excel without losing any data..
Author
Combine Text From Two Cells Into One
Repair MS Excel Blog
Combine Two Cells In Excel 2016
Publisher Logo
Merging Data From Multiple Cells In Excel
Margret
Margret Arthur is an entrepreneur & content marketing expert. She writes tech blogs and expertise on MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in easy to understand language is very impressive. When not writing, she loves unplanned travels.