Note that cell merging is best done before you have entered data into the cells, as Excel will only keep the top-left cell data if you attempt to merge cells that already contain data. If you need to merge cells that already contain data, then it is a good idea to copy and paste that data to other cells on the spreadsheet, then move the data. If you use the above options to merge cells in Excel, it will keep the text at the leftmost cell (A1 in this case) and remove the text from all other cells. If you don't want to lose the text from the merged cells in Excel, use the CONCATENATE Function (pre Excel 2019), the CONCAT Function (Excel 2019 & Office 365) or the ampersand. 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. Textrange1: This is the range whose cells have values you want to concatenate. Textrange2: The other ranges if you want to join in the text with commas. Retroarch configuration. Let's see an example to make things clear.
- Microsoft Excel Merge Cells Keep Data
- Excel Merge Cells But Keep Data
- Excel 2016 Merge Cells And Keep All Data
- Excel 2016 Merge Cells Keep Data
Excel is good for handling data, but there are times when you want to combine cells into one value or to split a string to make the data more manageable. Excel has several features which give us the ability to merge and split cells in multiple ways. To merge two cells or more without losing data, you cannot use the 'Merge Cells' command because Excel only keeps the data in the upper-left cell, you lose data in another cell. The solution is to use a formula or other solution that is easier and faster, with no formula losing no data. For more details, read the explanation below.
How to combine cells with line break / carriage return in Excel?
In Excel, we can combine multiple rows, columns or cells into one cell with the CONCATENATE function, and the combined contents can be separated by comma, dash, semicolon and other characters. But, have you ever tried to combine the cells with line break as following screenshot shown?
Combine multiple cells with line break with CONCATENATE and CHAR functions
In Excel, we can use the CONCATENATE to combine the cells then apply CHAR function to insert the line break. Please do as follows:
1. Enter the below formula into a blank cell - A6, in this case, then drag the fill handle right to the cells that you want to apply this formula, and all the rows have been combined into a cell in a line, see screenshot:
2. After combining the rows, then click Home > Wrap Text, and all the combined contents have been separated by line break, see screenshot:
Notes:
1. In the above formula, A1, A2, A3 are the cells value that you want to combine, and the CHAR(10) is used to enter line breaks in the cell.
2. If you need to combine multiple columns and cells into one cell, you just need to change the cell reference and join with the CHAR(10).
Combine multiple cells with line break with TEXTJOIN and CHAR functions
If you have Excel 2019 or later versions, the TEXTJOIN function can help you to solve this task quickly and easily.
1. Enter the below formula into a blank cell - A6, in this case, then drag the fill handle right to the cells that you want to apply this formula.
2. Then click Home > Wrap Text, and all the combined contents have been separated by line break, see screenshot:
Combine multiple cells into rows, columns or single cell with line break with a powerful feature
If there are multiple cells needed to be combined, the formula will be much complicated, here, I will introduce you an easy and handy tool--Kutools for Excel, with its Combine Rows, Columns or Cells without Losing Data feature, you can quickly merge multiple rows, columns or cells into a cell separated by comma, semicolon, line break and other characters.
After installing Kutools for Excel, please do as this:
1. Select the data range that you want to combine, and then click Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data, see screenshot:
2. In the popped out dialog box, do the following options:
- Select one combine type that you want to combine rows, columns or range of cellunder the To combine selected cells according to following options;
- Check New line under the Specify a separator section;
- Choose one place where to put the combined result, you can put it on left cell or right cell;
- Specify how you want to deal with the combined cells. You can keep or delete contents from those combined cells, and you can also merge those combined cells.
3. After finishing the above settings, please click Ok or Apply button, you will get the following result:
More relative articles:
- When you combine the cells from multiple columns or rows into a single cell, the combined data may be separated by nothing. But if you want to separate them with specified marks, such as space, commas, semicolon or others, how can you do?
- In Excel, you may always meet this problem, when you have a range of data which contains some duplicate entries, and now you want to combine the duplicate data and sum the corresponding values in another column.
- To combine or merge multiple columns into one long list, normally, you can copy and paste the columns data one by one into the specified column. But, here, I can introduce you some quick tricks to solve this task in Excel.
- In Excel worksheet, after concatenating two cell values with formulas, you may find it will not bold part of the text in the combined formula cell. This may be annoying in sometimes, how could you bold part text when concatenating two columns in Excel?
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails..
- 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 without losing Data; Split Cells Content; Combine Duplicate Rows/Columns.. 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..
- 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..
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- 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!
Tripboba.com - Are you a Microsoft Excel user? If so, there could be a day when you face some problems while trying to merge cells in Excel. For example, you need to merge some cells into a single cell in one row or column.
It is actually easy and simple to do merging in Excel. You may need to do this task every time you make tables in Excel. And it's no wonder when you have difficulty in merging the cells.
There is actually a merging menu button provided on your sheet toolbar that can help you to combine the cells. But, these options will only let you keep one of the cells' values. In other words, you will lose the other value on the cells.
Do you know you can actually keep them? To keep the data, you can use a formula on the cells you select. You will find it useful in many cases.
In this page, you will be served with the step by step tutorials on how to merge cells in Excel as well as how to combine texts in different columns. Scroll down to learn more!
Step-by-Step Tutorials How to Merge Cells in Excel
How to Merge Cells in Excel - Photo by eungyo seo from Pixabay
There are various options you can choose to merge cells in Excel. The first one is 'Merge and Center'. This mostly will function on your sheet's title.
The second is 'Merge Across' that combines a cell across columns, and the last is 'Merge Cells' in which it can help you to merge cells across columns and rows.
Here are more explanation and tutorials on how to merge cells in Excel you can follow:
Method 1. How to Merge and Center Cells in Excel
It helps you to combine cells in one row and get the content centered. It's mostly used for the sheet's title. Here's how to use this function:
Step 1. Drag to highlight the cells that you want to merge and center.
Step 2. On the toolbar, you can find the Alignment section. Find 'Merge and Center'. Single-click on it. Now, your cells are merged and centered into one cell.
Method 2. How to Merge Cells in Excel Horizontally
This function also helps you to merge and center cells, but in a horizontal line (across columns). When you choose cells with text, the text will be placed on the leftmost cells. Here is how to Merge Cells in Excel across columns:
Step 1. Drag to highlight the cells.
Step 2. Next to the 'Merge & Center' option, click on the arrow. Then, there will appear more merging options.
Step 3. Scroll down and choose 'Merge Across'. As for the result, the text will be placed on the left side of the merged cell.
1. In the above formula, A1, A2, A3 are the cells value that you want to combine, and the CHAR(10) is used to enter line breaks in the cell.
2. If you need to combine multiple columns and cells into one cell, you just need to change the cell reference and join with the CHAR(10).
Combine multiple cells with line break with TEXTJOIN and CHAR functions
If you have Excel 2019 or later versions, the TEXTJOIN function can help you to solve this task quickly and easily.
1. Enter the below formula into a blank cell - A6, in this case, then drag the fill handle right to the cells that you want to apply this formula.
2. Then click Home > Wrap Text, and all the combined contents have been separated by line break, see screenshot:
Combine multiple cells into rows, columns or single cell with line break with a powerful feature
If there are multiple cells needed to be combined, the formula will be much complicated, here, I will introduce you an easy and handy tool--Kutools for Excel, with its Combine Rows, Columns or Cells without Losing Data feature, you can quickly merge multiple rows, columns or cells into a cell separated by comma, semicolon, line break and other characters.
After installing Kutools for Excel, please do as this:
1. Select the data range that you want to combine, and then click Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data, see screenshot:
2. In the popped out dialog box, do the following options:
- Select one combine type that you want to combine rows, columns or range of cellunder the To combine selected cells according to following options;
- Check New line under the Specify a separator section;
- Choose one place where to put the combined result, you can put it on left cell or right cell;
- Specify how you want to deal with the combined cells. You can keep or delete contents from those combined cells, and you can also merge those combined cells.
3. After finishing the above settings, please click Ok or Apply button, you will get the following result:
More relative articles:
- When you combine the cells from multiple columns or rows into a single cell, the combined data may be separated by nothing. But if you want to separate them with specified marks, such as space, commas, semicolon or others, how can you do?
- In Excel, you may always meet this problem, when you have a range of data which contains some duplicate entries, and now you want to combine the duplicate data and sum the corresponding values in another column.
- To combine or merge multiple columns into one long list, normally, you can copy and paste the columns data one by one into the specified column. But, here, I can introduce you some quick tricks to solve this task in Excel.
- In Excel worksheet, after concatenating two cell values with formulas, you may find it will not bold part of the text in the combined formula cell. This may be annoying in sometimes, how could you bold part text when concatenating two columns in Excel?
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails..
- 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 without losing Data; Split Cells Content; Combine Duplicate Rows/Columns.. 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..
- 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..
- More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- 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!
Tripboba.com - Are you a Microsoft Excel user? If so, there could be a day when you face some problems while trying to merge cells in Excel. For example, you need to merge some cells into a single cell in one row or column.
It is actually easy and simple to do merging in Excel. You may need to do this task every time you make tables in Excel. And it's no wonder when you have difficulty in merging the cells.
There is actually a merging menu button provided on your sheet toolbar that can help you to combine the cells. But, these options will only let you keep one of the cells' values. In other words, you will lose the other value on the cells.
Do you know you can actually keep them? To keep the data, you can use a formula on the cells you select. You will find it useful in many cases.
In this page, you will be served with the step by step tutorials on how to merge cells in Excel as well as how to combine texts in different columns. Scroll down to learn more!
Step-by-Step Tutorials How to Merge Cells in Excel
How to Merge Cells in Excel - Photo by eungyo seo from Pixabay
There are various options you can choose to merge cells in Excel. The first one is 'Merge and Center'. This mostly will function on your sheet's title.
The second is 'Merge Across' that combines a cell across columns, and the last is 'Merge Cells' in which it can help you to merge cells across columns and rows.
Here are more explanation and tutorials on how to merge cells in Excel you can follow:
Method 1. How to Merge and Center Cells in Excel
It helps you to combine cells in one row and get the content centered. It's mostly used for the sheet's title. Here's how to use this function:
Step 1. Drag to highlight the cells that you want to merge and center.
Step 2. On the toolbar, you can find the Alignment section. Find 'Merge and Center'. Single-click on it. Now, your cells are merged and centered into one cell.
Method 2. How to Merge Cells in Excel Horizontally
This function also helps you to merge and center cells, but in a horizontal line (across columns). When you choose cells with text, the text will be placed on the leftmost cells. Here is how to Merge Cells in Excel across columns:
Step 1. Drag to highlight the cells.
Step 2. Next to the 'Merge & Center' option, click on the arrow. Then, there will appear more merging options.
Step 3. Scroll down and choose 'Merge Across'. As for the result, the text will be placed on the left side of the merged cell.
Method 3. How to Merge Rows and Columns in Excel
Microsoft Excel Merge Cells Keep Data
This merging option allows you to merge cells in both columns and rows. Here is how to merge cells in Excell for columns and rows options:
Step 1. Drag to highlight the cells.
Step 2. Next to the 'Merge & Center' option, click on the arrow. Then, there will appear more merging options.
Step 3. Choose 'Merge Cells'. Now, your cells will get merged to the upper-left of the merged cells.
Method 4. How to Unmerge Cells in Excel
Excel Merge Cells But Keep Data
Now, that you've learned how to merge cells in Excel, you need also to learn how to unmerge the cells. This will help you to fix the unwanted merged cells on your sheet. Here is how:
Excel 2016 Merge Cells And Keep All Data
Step 1. Click or drag the merged cells.
Step 2. Click on the more options of 'Merge & Center' on the toolbar.
Step 3. Scroll down and choose to unmerge. Now, your cells are unmerged.
Excel 2016 Merge Cells Keep Data
Method 5. How to Merge Cells in Excel without Losing Data
Can you merge cells in Excel without losing the value? Yes, it is possible. This is possible when a table in excel and you want to merge two of the columns into one. As for the result, the value will get merged instead. Here is how to merge cells in Excel without losing data inside:
Step 1. Select one cell to put the merged values.
Step 2. In the cell, type = and then select on the targeted first cell.
Step 3. Type & and also quotation mars '. Give space to enclose it.
Step 4. Single-click on the second cell. Then press enter. Now, you have made a formula to combine a text. The right example of the formula will be like this =F5&' '&G5.
Easy, isn't it? Learn more helpful tutorials provided by Tripboba here.
