If you would like to join or summarize cell values when merging cells, use the Merge Cells tool available in Text Toolkit for Excel.
To start working with Merge Cells, open your Excel and click the Text Toolkit icon on the ribbon:
The add-in pane will appear. Find and click Merge Cells there:
Select a range with the cells to be merged and choose options that suit your task:
- This field shows what range is going to be processed.
- The How to merge drop-down menu contains three lines: Columns into one, Rows into one, Cells into one:
Decide which option corresponds to the desired result and select it from the list.
- For the values to be merged, you can use either a delimiter or a function.
The drop-down list under Combine with offers the following preset delimiters: a space, a comma, a semicolon, a period, and a line break:
If you need another delimiter, just type it into the Combine with field.
The delimiters you've used are shown under History:
The functions to choose between are as follows: AVERAGE, COUNT, COUNTA, MAX, MIN, and SUM.
- The drop-down list under Place the results to changes according to the option selected under How to merge.
If you merge columns into one, you can pick either the left or right column as a destination for the values to be merged:
If you merge rows into one, you can choose between the top row and the bottom one:
When you merge cells into one, there are four options available:
- With a check mark next to Clear the contents of the selected cells, only the resulting values will be shown. The original data will disappear from the processed cells.
- If you select the Merge all areas in the selection option, not only values but also cells themselves will be merged.
- Check Skip empty cells to make the tool ignore cells that contain nothing.
- Pick Wrap text to get a result string displayed line by line in a cell.
- With the Create a backup copy option selected, the Restore button is added and a hidden copy of your sheet will be saved. After the tool has processed your data, the Restore button gets enabled. You can click it and return to the previous version of the processed sheet if necessary.
Tip. To see available backup copies, right-click any sheet tab in your Excel workbook and choose "Unhide".
Note. You can delete hidden backups with the Remove all backup sheets option in the More menu. To see it, click the three dots in the lower-right corner of the Text Toolkit pane:
When you're done with the settings, click the Merge button.