To combine the contents of cells in Excel without deleting the originals, enter a formula such as =A2&" "&B2 in an empty cell, replacing A2 and B2 with your cell references. Use TEXTJOIN to combine a range, or use Merge & Center only when you want a larger visual cell and are willing to keep just the upper-left cell’s content. First decide whether you mean joining text, adding numbers, or merging the cell layout—these are different tasks.
What You’ll Need
- A workbook in Excel for Microsoft 365 on Windows. The formulas described here also work in current Excel for Mac and Excel for the web.
- The cells you want to combine, plus an empty destination cell or column. Keeping the source cells intact makes it easier to check your results and correct mistakes.
- A clear idea of the separator you want between items, such as a space, comma, hyphen, or line break.
Step-by-Step
- Identify what “combine” means for your task. Joining cell contents places text or values together in one cell—for example, first and last names. Adding combines numeric values into a total. Merging changes the worksheet layout by making selected cells appear as one larger cell. If you are unsure, join or total the data with a formula first; unlike merging, a formula does not erase the source values.
- Check the source cells and choose a destination. Make sure the cells contain the information you want and that the destination cell is empty. For example, suppose A2 contains Maya and B2 contains Chen. Select C2 as the destination. If the source columns have headings, such as First Name and Last Name, put a heading like Full Name above the new results so the combined column is easy to understand.
- Join two cells with an ampersand. With the first name in A2 and the last name in B2, type
=A2&" "&B2in C2 and press Enter. The formula inserts a space between the values, so the result is Maya Chen. Use&"-"&between references for a hyphen, or&", "&for a comma and space. Without a separator, the contents run together. - Join several cells with TEXTJOIN. If A2 contains Maya, B2 contains L., and C2 contains Chen, enter
=TEXTJOIN(" ",TRUE,A2:C2)in D2. The result is Maya L. Chen. The first argument is the separator; TRUE tells Excel to ignore empty cells; and A2:C2 is the range to join. Change the separator to", "if you want comma-separated content. TEXTJOIN is available in Microsoft 365 and supported modern Excel versions, including Excel 2019 and later. - Choose how blank cells should behave. In the TEXTJOIN example, TRUE skips empty cells instead of adding an extra separator for each blank. If you need empty cells included in the join, use FALSE instead. Check the result when some cells may be blank: skipping blanks generally produces cleaner names and addresses, while keeping them can matter when the position of each field has meaning.
- Combine the rest of the rows. After checking the first result, select its cell and point to the small square at the lower-right corner of the selection, called the fill handle. Drag it down the destination column, or double-click it if the neighboring source column has continuous data. Excel adjusts relative references for each row. You can also copy the formula, select the cells below it, and paste. Review several results, especially where a source cell is blank or contains an unexpected value.
- Use Flash Fill for a one-time pattern. In a new column, type the desired combined result for the first row, then start the next example if Excel needs another pattern to recognize. Choose Data > Flash Fill, or press Ctrl+E on Windows. Flash Fill creates fixed text, not a formula that updates when the source changes. It is available in desktop Excel; if you are using Excel for the web and do not see the command, use a formula instead. On Mac, check Data > Flash Fill in the ribbon; keyboard shortcuts can differ by keyboard and Excel version.
- Add values when you mean a total. For example, if B2:B5 contains 12, 8, 5, and 10, enter
=SUM(B2:B5)in B6 or another empty cell. The result is 35. SUM adds numbers; it does not join their displayed contents into a text string. If you need both the original numbers and the total, keep the result in a separate cell rather than replacing the source data. - Keep a formula result or turn it into fixed text. A formula result updates when its source cells change. To retain the current result as a fixed value, copy the destination cells, then choose Home > Paste > Paste Values. This replaces the formulas in the copied destination with their displayed results while leaving the original source cells alone. On Mac, use the Paste Values option on the Home tab or the paste options menu; in Excel for the web, use Home > Paste > Paste Values when available.
- Merge cells only for layout. Select the cells you want to turn into one larger cell, then choose Home > Merge & Center ▼ > Merge Cells. Pick Merge & Center if you also want the content centered. Excel keeps the content from the upper-left cell and discards content in the other selected cells, so copy or join those values somewhere safe first. Merging is often suitable for a heading above a table, but usually not for data columns you need to sort or filter.
Common Mistakes
Using Merge & Center to join text. Merging does not combine all selected cell contents. Excel preserves the upper-left entry and warns that other values will be removed. Cancel the merge if you see that warning unexpectedly, then use an ampersand formula or TEXTJOIN in a separate cell.
Forgetting a separator. A formula that directly joins two cells can produce an unreadable result such as MayaChen or 14OakStreet. Add the intended space or punctuation between references, and check whether the separator should appear when one of the source cells is blank.
Overwriting the source data too soon. A formula placed over one of its source cells can cause a circular reference or destroy data. Put the result in an unused column, verify it, and only then decide whether to keep a formula or paste values.
Expecting a formula to update after pasting values. Paste Values deliberately removes the formula. If the source changes later, a fixed result will not recalculate; keep the formula if the combined content needs to stay current.
Confusing text joining with arithmetic. Joining numeric cells creates a text string, while SUM calculates a total. For example, joining 12 and 8 with a space produces 12 8; summing them produces 20. Choose based on the intended result.
Tips
- Use a space for names, a comma and space for lists, and a hyphen for codes only when that separator is part of the required format.
- For large tables, turn the data into an Excel table with Home > Format as Table. A formula entered in a table column usually fills down automatically for the other rows.
- Keep source columns until you have checked the combined results. If you later delete source columns, make sure you have saved any information or formulas you still need.
- If the result looks like a date or number but should be text, check the source values and number formats. Joining values creates text, and Excel may display dates according to their underlying serial values if they were not formatted or converted as expected.
- For addresses or other multi-part details, build and test the formula on one representative row first. Rows with missing apartment numbers, middle initials, or other optional fields are useful checks.
Troubleshooting
The formula appears as text instead of calculating. Check that the cell does not have the Text number format. Change it to General from Home > Number Format, then press F2 and Enter to make Excel evaluate the entry. Also confirm the formula starts with an equals sign and that Show Formulas is not turned on. On Windows, Ctrl+` toggles Show Formulas; on Mac, the shortcut may vary, so check Formulas > Show Formulas.
The result has no spaces or punctuation. Add a separator between references. For an ampersand formula, make sure the separator is inside quotation marks and that the ampersands are outside them. For TEXTJOIN, the separator is its first argument. If the result still looks wrong, check whether the source cells themselves contain leading or trailing spaces.
TEXTJOIN returns an error or is not recognized. Check the spelling and the Excel version. TEXTJOIN is not available in some older releases. In those versions, join a smaller number of cells with ampersands, adding separators between each reference. If Excel uses semicolons rather than commas in formulas due to regional settings, follow the separators accepted by your installation.
Some rows do not fill down correctly. Confirm that you filled the formula from the correct starting row and that each source reference points to the intended row. Relative references such as A2 change as a formula is copied. If a reference must stay fixed, add dollar signs, such as $A$2, but do so only when every result should use that same cell.
Excel warns that a merge will discard data. Choose Cancel, then inspect every selected cell. Copy any values you need, or create a combined formula in a separate cell and verify its result before merging. If the warning appears after you have already merged, use Ctrl+Z on Windows or Command+Z on Mac immediately to undo, provided you have not performed other actions you need to keep.
Frequently Asked Questions
Does combining cells delete the original values? Joining cells with a formula does not delete the source values when you put the formula in a separate cell. Merging selected cells does discard content outside the upper-left cell, so avoid merging when every value matters.
Can I combine cells without a formula? Yes. Flash Fill can create fixed results from a pattern you provide in desktop Excel. Copying and pasting values is another way to freeze results after a formula has created them. Neither method keeps the combined result linked to changing source cells.
Can I combine text and numbers in the same cell? Yes. Ampersands and TEXTJOIN can join text with numeric values, but the final result is text. Check how dates, percentages, and currency appear; you may need to format or convert a value before joining it so it displays in the form you want.
How do I combine cells while ignoring blanks? Use TEXTJOIN with TRUE as its second argument. For example, with optional parts in A2:C2, =TEXTJOIN(" ",TRUE,A2:C2) skips empty cells and separates the remaining entries with spaces.
Can I combine matching categories from multiple rows? Yes, but that is a grouping task rather than simply joining cells side by side. For a small list, filter the category and join its matching entries in a separate result cell. For recurring or large datasets, use Power Query to group the records, or build a formula suited to your Excel version and data layout.
Will merged cells work well in a sortable table? Usually not. Merged cells can interfere with sorting, filtering, and selecting a consistent range. Keep one value per cell in the data itself, and use cell formatting or a separate heading area if you want a visual layout that spans columns.
Related
- How to Change Tab Color in Excel: A Practical Guide
- Change Table Color in Excel: Styles and Custom Formatting
- Excel Subscript: How to Change Text to Subscript
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Change Google Sheets Date Format to AustraliaTo use Australian dates in Google Sheets, open the spreadsheet in a browser and choose File > Settings.…
- Edit a Slicer in Excel: Change Its Style, Size, and SettingsTo edit a slicer in Excel for Microsoft 365 on Windows, click the slicer and use the contextual…
- Delete Data Validation in Excel: Steps and OptionsTo delete data validation in Excel for Microsoft 365 on Windows, select the cell or range, choose Data…
- Why Is Excel Not Letting Me Transpose Data?Excel usually will not transpose data because the source was not copied as a normal cell range, or…
- Make a Flowchart in Word with SmartArt: Steps and LimitsYes. In Word for Microsoft 365, choose Insert > SmartArt, select a Process layout, and enter your steps;…
- Excel Dependent Drop-Down Lists with Dynamic RangesYes. In Excel for Microsoft 365, make the source data an Excel Table, use a FILTER formula to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.