To merge cells in Excel without losing their contents, first combine the contents in a helper cell with TEXTJOIN, copy that result, and paste it as a value into the upper-left cell of the range. Then merge the cells. Excel’s Merge & Center command keeps only the upper-left cell’s content, so combining the data first is the key safeguard. These steps are for Microsoft Excel for Microsoft 365 on Windows.
What You’ll Need
- An Excel workbook with the cells you want to combine.
- A blank helper cell outside the range you plan to merge.
- A copy of the original workbook, or another backup of the source data. Merging can clear content from cells other than the upper-left one.
If your aim is to combine rows from several worksheets or files into a single dataset, that is different from merging cells for layout. The steps below combine the contents of a selected cell range and, if desired, merge those cells into one visual cell.
Step-by-Step
- Save a backup and inspect the range. Save a copy of the workbook before making changes. Identify the exact cells to combine, and check that you are not including unrelated data. For example, if a heading is spread across A1, B1, and C1, the range is A1:C1. Excel will keep the value in A1 when that range is merged and discard the contents in B1 and C1 unless you preserve them first.
- Choose how the contents should be separated. Decide whether the combined result should have spaces, commas, line breaks, or another separator. A heading such as “Quarterly” in A1 and “Sales” in B1 may read best with a space. For names or labels, a comma or hyphen might be clearer. Decide how blank cells should be handled too; the method below skips them.
- Enter a TEXTJOIN formula in a helper cell. With the values to combine in A1:C1 and a blank helper cell in D1, enter
=TEXTJOIN(" ",TRUE,A1:C1)in D1. The space inside the first pair of quotation marks is the separator; TRUE tells Excel to ignore empty cells. Change the separator to suit your data. For example, use a comma followed by a space if that is the style you want. Keep the helper cell outside the range you intend to merge. - Check the formula result before changing the original cells. D1 should display all nonblank contents from A1:C1, in order, with a space between them. Check spelling, punctuation, and spacing. If a value is missing, verify that the formula includes the full range and that the value is actually in one of those cells. If the result is not readable, adjust the separator before continuing.
- Copy the combined result. Select D1 and press Ctrl+C on Windows. On a Mac, press Command+C. You can also use Home > Copy. Copying the formula result is fine, but the next step must paste it as a value so the combined text remains in place when the source cells are merged.
- Paste the result into the upper-left cell as a value. Select A1, the upper-left cell of A1:C1. On Windows, choose Home > Paste > Values. In Excel for Mac, use Home > Paste or Edit > Paste Special and choose Values, depending on the ribbon layout. This replaces A1 with the combined result as fixed text, rather than leaving a formula that refers to cells about to be merged. Confirm that A1 now contains the complete combined text.
- Merge the selected range. Select A1:C1, then choose Home > Merge & Center. If you want the text centered, use that command. If you want it left-aligned, open the dropdown beside Merge & Center and choose Merge Cells. Excel may show a warning that only the upper-left value will be kept. Since you have already placed the combined text in A1, check the message and proceed only if the displayed result in A1 contains everything you need.
- Verify the result and remove the helper if appropriate. The merged cell should display the full text, and the former cells B1 and C1 should no longer contain separate values. Check the merged cell in the formula bar as well as on the worksheet, since a narrow column or row may make text appear clipped. Once you are satisfied, clear D1 if it is no longer needed. Keep the backup until you have checked the workbook.
To combine contents without creating a merged cell, stop after pasting the result as a value and leave the source cells unchanged, or paste the result into a separate destination cell. This is often a better choice for data tables because each record remains in its own cell and sorting, filtering, copying, and formulas continue to work more predictably.
Common Mistakes
- Clicking Merge & Center before combining the contents. Excel does not automatically join the values in the selected cells. It keeps the upper-left value and removes the other cell contents. Undo may recover them immediately, but it is not a reliable substitute for a backup, especially after further edits.
- Putting the helper formula inside the range to be merged. If the formula cell is part of the selected range, merging may remove it or alter the result. Put the helper in a blank cell outside the selection, such as D1 when combining A1:C1.
- Leaving the result as a formula linked to the source cells. A formula that references cells in the range can change or produce an unexpected result after the range is merged. Paste the result as a value into the upper-left cell before merging.
- Choosing a separator without checking the final wording. A missing separator can turn “North” and “Region” into “NorthRegion.” Too many spaces or punctuation marks can make a label look untidy. Preview the result in the helper cell before replacing anything.
- Merging cells in a table that needs sorting or filtering. Merged cells can interfere with sorting, filtering, selecting ranges, and filling formulas. For a list of records, keep each value in its own cell. Use alignment or formatting options for appearance instead of merging data cells.
- Assuming dates and numbers will retain their display format. TEXTJOIN combines displayed values as text, and dates or numbers may not appear in the desired format in the result. Check them before pasting. If the exact date or number format matters, prepare the text representation deliberately or keep the original values in separate cells.
Tips
- Use a separator that makes the result understandable. A space suits a multiword heading, while a comma and space can distinguish names or categories. For a compact label, a hyphen or slash may be appropriate. Avoid a separator that could be confused with punctuation already present in the data.
- Prefer Center Across Selection for many headings. If you only want text centered over several columns without making a merged cell, select the range, open the Format Cells dialog with Ctrl+1 on Windows, choose the Alignment tab, and set Horizontal to Center Across Selection. On a Mac, use Command+1 to open Format Cells. This preserves the individual cells, which can be useful in a worksheet used for calculations or analysis.
- Use Merge Across only when that is the layout you actually want. The Merge & Center dropdown includes Merge Across, which merges cells separately across each row in the selected range. It is not the same as combining every selected cell into one cell. As with other merge options, check each row’s contents first because only the leftmost cell in each row is retained.
- Keep a separate copy of original values when they matter. The merged cell stores one combined value; it does not preserve the original values as individually editable entries. Save them on another sheet or in the backup workbook if you might need to sort, calculate, or reuse them separately.
- Check the whole worksheet after merging. Review nearby formulas, print layout, row height, and column width. Merging changes the worksheet’s structure and appearance, and the combined text may need wrapping or adjusted dimensions to display clearly.
Troubleshooting
Excel warns that merging keeps only the upper-left value. That warning is expected. Cancel if you have not yet combined the contents. Create the helper result, paste it as a value into the upper-left cell, and then merge. If you already continued, press Ctrl+Z on Windows or Command+Z on Mac immediately, before making other changes, and check whether the original contents return.
The combined result is missing a value. Confirm that the formula’s range includes every source cell and that there are no values outside it. Check for unexpected spaces or formulas returning empty text. If you pasted into the wrong destination, undo the paste and repeat the process using the correct upper-left cell and helper location.
The formula appears as text or returns an error. Make sure the cell is not formatted as Text, and check that the entry begins with an equals sign and uses straight quotation marks. If the formula is displayed instead of calculated, change the cell format to General, re-enter the formula, and press Enter. Also check that the formula uses commas between arguments in your Excel regional settings; some locales use semicolons instead.
The date or number looks different after combining. A combined result is text, not a set of separately formatted values. The source cell’s display format may not carry over as you expect. Undo if necessary, decide on the exact wording or number format you need, and prepare the result accordingly. If calculations or date arithmetic are required later, do not replace the underlying values with combined text.
Merge & Center is unavailable. Check whether the worksheet is protected, whether you are editing a cell, or whether the selected range is inside an Excel table. Some operations, including merging, are restricted in these situations. Finish editing or select a suitable range outside the table, or review the sheet’s protection settings if you are authorized to change them. Do not remove protection without permission.
The text seems cut off after merging. Select the merged cell and inspect the full content in the formula bar. Increase the column width or row height, or turn on Wrap Text from Home > Wrap Text. If the worksheet still does not display the full text cleanly, use a wider range or avoid merging.
Frequently Asked Questions
Does Merge & Center combine the text in Excel?
No. It keeps the content from the upper-left cell and discards the contents of the other selected cells. Combine the values first if you need to preserve them.
Can I merge cells without losing any information?
Yes, if you first join the contents into one value and place that result in the upper-left cell. The merged cell will contain the combined text, but the original values will no longer be separate cells, so keep a backup if you need them individually.
Can I combine cells without merging them?
Yes. Create the combined result in a helper or destination cell and leave the original cells alone. This is usually preferable for records that need to be sorted, filtered, or used in calculations.
What happens to blank cells when I use TEXTJOIN?
In the example formula, TRUE tells TEXTJOIN to ignore empty cells. The function joins the nonblank values in the specified range using the separator you chose.
Can I undo a merge?
Immediately after merging, use Ctrl+Z on Windows or Command+Z on Mac. If you have made more edits since then, undo could reverse those changes too. A saved backup is the safer way to recover original cell contents.
Is merging cells a good idea in a data table?
Usually not. Merged cells can complicate sorting, filtering, and formulas. Keep data in separate cells and use formatting or Center Across Selection when you only need a heading to span columns visually.
Related
- Import Excel into a SharePoint List: Step-by-Step
- How to Highlight a Row in Excel with a Keyboard Shortcut
- How to Import an Excel Document Into Microsoft Word
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Ordinal Number Format: Show 1st, 2nd, and 3rdExcel does not have a built-in number format that automatically changes a number to an ordinal such as…
- Google Sheets COUNTIF: Count Cells That Contain TextYes. In Google Sheets, use COUNTIF with asterisks around the text to count cells that contain it anywhere:…
- How to Freeze Two Columns in Excel While ScrollingTo keep columns A and B visible while you scroll horizontally in Excel for Microsoft 365 on Windows,…
- Multi-Criteria XLOOKUP in Google Sheets: How to Use ItIn Google Sheets, use XLOOKUP with multiplied condition tests to find a row matching more than one criterion.…
- Freeze Heading Row in Excel: Keep Column Labels VisibleTo keep your column labels visible as you scroll in Excel, select View > Freeze Panes > Freeze…
- How to Change the View in the Microsoft Outlook Email AppTo change how your inbox looks, open Outlook’s view controls and choose the layout or message-list options you…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.