How to merge cells, columns and text in Excel
By the Analistable team · Updated · 11 min read
Merge & Center joins cells into one big cell but keeps only the upper-left value. To combine the *contents* of cells or columns, use a formula: =A2&" "&B2, =CONCAT(A2," ",B2) or =TEXTJOIN(" ",TRUE,A2:C2), then copy and Paste Values. For headings, Center Across Selection looks like a merge without breaking sorting or filtering.
Merging cells vs combining their contents
People say “merge” for two different things:
- Merging cells is formatting: several cells become one larger cell. Excel keeps only the value in the upper-left cell and deletes the rest. See how to merge cells in Excel.
- Combining contents joins the values: “Ada” and “Lovelace” become “Ada Lovelace” in one cell. This needs a formula or Flash Fill. See combining two columns and combining text.
Formulas for combining text
| Formula | Result for A2=Ada, B2=Lovelace | Available in |
|---|---|---|
| =A2&" "&B2 | Ada Lovelace | All versions, Google Sheets |
| =CONCATENATE(A2," ",B2) | Ada Lovelace | All versions, Google Sheets |
| =CONCAT(A2," ",B2) | Ada Lovelace | Excel 2019+, 365 (Sheets: two values only) |
| =TEXTJOIN(" ",TRUE,A2:B2) | Ada Lovelace | Excel 2019+, 365, Google Sheets |
TEXTJOIN is the most practical: it takes a range, a delimiter and an option to skip empty cells, so you don't get double spaces where a middle name is missing.
Keep the result, drop the formula
- Fill the formula down the helper column.
- Copy the column, then Paste Special → Values (Ctrl+Alt+V, then V).
- Delete the original columns if you no longer need them.
Flash Fill: combining by example
Type the combined value for the first row, start typing the second, and press Ctrl+E (Data → Flash Fill). Excel spots the pattern and fills the column. It is quick but static: it won't update if the source cells change.
Merge & Center and its alternatives
Merged cells break sorting, filtering, copying ranges and many formulas. For headings over several columns, use Center Across Selection (Format Cells → Alignment → Horizontal) instead: the text looks centred across the columns, but every cell stays separate. See Merge & Center in Excel and Center Across Selection.
The same in Google Sheets
Google Sheets merges cells with Format → Merge cells, and combines text with &, CONCATENATE, TEXTJOIN and JOIN. CONCAT accepts only two values. See merging cells in Google Sheets and CONCATENATE in Google Sheets.
Worked example: building full names and labels
| First | Middle | Last | Formula | Result |
|---|---|---|---|---|
| Ada | Lovelace | =A2&" "&B2&" "&C2 | Ada Lovelace (double space) | |
| Ada | Lovelace | =TEXTJOIN(" ",TRUE,A2:C2) | Ada Lovelace | |
| Grace | Brewster | Hopper | =TEXTJOIN(" ",TRUE,A3:C3) | Grace Brewster Hopper |
| Grace | Brewster | Hopper | =C3&", "&LEFT(A3)&"." | Hopper, G. |
TEXTJOIN's second argument (TRUE) skips the empty middle name, which is why it's the safest default when some parts may be missing.
Splitting is the reverse
If you need to undo a combine — split “Ada Lovelace” back into two columns — use Data → Text to Columns with a space delimiter, Flash Fill (Ctrl+E) by example, or =TEXTSPLIT(A2, " ") in Microsoft 365. Keeping the original columns and combining only in a helper column avoids the problem altogether.
Common mistakes
- Deleting the source columns while the combined column still contains formulas — every result becomes #REF!. Paste as values first.
- Combining dates and numbers without TEXT(), which shows serial numbers like 46028 instead of dates.
- Merging cells in a data range that you'll later sort or filter.
- Using CONCAT in Google Sheets with more than two values — it only accepts two there.
Which method should you use?
- A heading over several columns → Center Across Selection.
- Two columns into one, permanently → & or TEXTJOIN, then Paste Special → Values.
- A quick one-off on clean data → Flash Fill (Ctrl+E).
- Data you refresh every month → Power Query Merge Columns.
The four merge options in Excel
The arrow next to Home → Merge & Center opens four commands. They are in the same place in Excel for Windows and Mac, and on Windows you can reach them from the keyboard with the ribbon key tips shown.
| Command | What it does | Windows keys |
|---|---|---|
| Merge & Center | Merges the whole selection into one cell and centres the text | Alt, H, M, C |
| Merge Across | Merges each selected row separately, keeping the rows apart | Alt, H, M, A |
| Merge Cells | Merges the selection without changing alignment | Alt, H, M, M |
| Unmerge Cells | Splits merged cells back into separate cells | Alt, H, M, U |
Merge Across is handy for a form with a long label on several rows: select B2:E6 and each row becomes one wide cell, rather than one huge block. Every merge command keeps only the upper-left value of each merged area and warns you before discarding the others.
Finding and unmerging merged cells in a data range
Reports exported from other systems often arrive with merged cells — a region name merged down over all of its rows, for example. Before you can sort, filter or pivot, unmerge them and fill the gaps:
- Press Ctrl+F, click Options, then Format. On the Alignment tab tick Merge cells, click OK and then Find All. The list shows every merged area; press Ctrl+A to select them all.
- Choose Home → Merge & Center → Unmerge Cells. The value stays in the top cell of each former merge and the cells below are empty.
- With the column still selected, choose Home → Find & Select → Go To Special → Blanks.
- Type
=and press the up arrow, so the active blank cell refers to the cell above, then press Ctrl+Enter to enter that formula in every selected blank. - Copy the column and paste it as values, so the filled names no longer depend on the cells above.
After step 4 every row carries its region, and the data can be sorted or joined to another table. Check that no genuinely empty cells were selected in step 3, because they will be filled from the row above too.
Combining text with numbers, dates and codes
Joining a number or a date to text uses the underlying value, not what the cell displays. A date of 15 March 2026 joined with & becomes 46096, and 1250.5 formatted as currency becomes plain 1250.5. Wrap each value in TEXT with a format code:
| Formula | Result |
|---|---|
| ="Date: "&A2 | Date: 46096 |
| ="Date: "&TEXT(A2,"dd mmm yyyy") | Date: 15 Mar 2026 |
| ="Total: "&TEXT(B2,"£#,##0.00") | Total: £1,250.50 |
| ="ID-"&TEXT(C2,"00000") | ID-00742 |
| ="Margin "&TEXT(D2,"0.0%") | Margin 12.5% |
The 00000 code restores leading zeros that Excel strips from numbers, which matters for product codes and postcodes. In some language versions of Excel the date codes are localised, so a formula written in English may need different letters for day, month and year when it is opened elsewhere; check the result after sharing a workbook.
Line breaks and other separators
To put each part on its own line within one cell, use CHAR(10) as the separator and turn on Wrap Text: =TEXTJOIN(CHAR(10), TRUE, A2:D2). Without Wrap Text the line breaks are there but the cell shows everything on one line. The same CHAR(10) works in Google Sheets. For comma-separated values, use ", " as the delimiter; for a value that will be pasted into another system, choose a separator that never appears in the data, such as a pipe "|".
Worked example: an address block from five columns
| Name | Line 1 | Line 2 | Town | Postcode |
|---|---|---|---|---|
| Cedar Books | 12 Mill Lane | York | YO1 7HU | |
| Bloom Florist | Unit 4 | Station Yard | Leeds | LS1 4DY |
In F2, =TEXTJOIN(CHAR(10), TRUE, A2:E2) with Wrap Text on gives four lines for Cedar Books — the empty Line 2 is skipped — and five for Bloom Florist. A one-line version for a mailing list is =TEXTJOIN(", ", TRUE, B2:E2), which returns "12 Mill Lane, York, YO1 7HU". With the & operator the empty Line 2 would leave a stray ", ," in the first address, which is exactly the problem TEXTJOIN's skip-empty argument solves.
Combining values from many rows into one cell
TEXTJOIN also works down a column. =TEXTJOIN(", ", TRUE, A2:A50) lists every value in one cell. To list only the rows that meet a condition, add IF or FILTER:
=TEXTJOIN(", ", TRUE, IF(B2:B50="North", A2:A50, ""))
=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(A2:A50, B2:B50="North")))The first works in Excel 2019 and later; in Excel 2019 confirm it with Ctrl+Shift+Enter, while Microsoft 365 enters it normally. The second needs Microsoft 365 or Google Sheets and also removes repeats. An Excel cell holds at most 32,767 characters, so TEXTJOIN over a very long column can return #VALUE!. For grouping one row per key, see combining duplicate rows and TEXTJOIN for matched rows.
Merging columns in Power Query
- Select a cell in the data and choose Data → From Table/Range to open the Power Query editor.
- Ctrl-click the columns in the order you want them joined.
- Choose Transform → Merge Columns to replace them with one column, or Add Column → Merge Columns to keep the originals.
- Pick a separator (space, comma, tab or a custom one) and name the new column.
- Click Close & Load. When new rows are added to the source table, Refresh combines them too.
If some columns contain empty values, check the result for doubled separators. An alternative is a custom column with Text.Combine({[First], [Middle], [Last]}, " "), which leaves out null values. Numbers must be converted to text first, for example with Text.From([Units]).
Versions and platforms
| Feature | Excel 2016 | Excel 2019 / 2021 | Microsoft 365 | Google Sheets |
|---|---|---|---|---|
| & and CONCATENATE | Yes | Yes | Yes | Yes |
| CONCAT and TEXTJOIN | No | Yes | Yes | Yes (CONCAT takes two values) |
| TEXTSPLIT | No | No | Yes | Use SPLIT |
| Flash Fill | Yes | Yes | Yes | Smart Fill suggestions instead |
| Center Across Selection | Yes | Yes | Yes | No |
If a workbook will be opened in Excel 2016, avoid TEXTJOIN — it shows #NAME? there — and fall back on & with TRIM to tidy double spaces. In Google Sheets, a single =ARRAYFORMULA(IF(A2:A="", , A2:A&" "&B2:B)) at the top of a column combines every row and fills new rows automatically.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| #NAME? with TEXTJOIN or CONCAT | Excel 2016 or older | Use & or CONCATENATE |
| The formula shows as text instead of a result | The cell was formatted as Text before typing | Set it to General, then press F2 and Enter |
| A date appears as a five-digit number | The date was joined without TEXT | Wrap it: TEXT(A2,"dd/mm/yyyy") |
| Leading zeros have gone | The code is stored as a number | Use TEXT(A2,"00000"), or store codes as text |
| Sorting fails with a message about merged cells | The range contains merged cells of different sizes | Unmerge, fill the blanks, then sort |
| Line breaks don't show | Wrap Text is off | Turn on Wrap Text for the result column |
| Results turn to #REF! after deleting columns | The formulas still referred to the deleted columns | Paste as values before deleting |
| Error about the number of arguments in Google Sheets | CONCAT given more than two values | Use CONCATENATE, TEXTJOIN or & |
Center Across Selection step by step
- Type the heading in the leftmost cell, for example A1, and leave B1:F1 empty.
- Select A1:F1.
- Open Format Cells with Ctrl+1 on Windows or Cmd+1 on a Mac.
- On the Alignment tab, set Horizontal to Center Across Selection and click OK.
The heading now sits centred over the six columns, yet each cell can still be selected, sorted and copied on its own. If you type something into B1 later, the centring stops at that cell, which is a useful reminder that the space is no longer empty. There's no equivalent in Google Sheets, so there a merged header row — kept above the data, never inside it — is the usual compromise.
Merging cells in Google Sheets in more detail
Select the cells and choose Format → Merge cells, then Merge all, Merge horizontally or Merge vertically; the toolbar's merge button offers the same choices. Merge horizontally works like Excel's Merge Across, one merged cell per row. As in Excel, only the top-left value is kept and Sheets asks you to confirm before discarding the rest. Sorting a range that contains vertically merged cells is refused, so the same advice applies: keep merges in headings and out of the data.
Splitting combined text step by step
When you need to reverse a combine, choose the method by version:
- Text to Columns (all Excel versions): select the column, choose Data → Text to Columns, pick Delimited, tick Space or enter another delimiter, and set the destination to an empty column so the original stays intact.
- TEXTSPLIT (Microsoft 365):
=TEXTSPLIT(A2, " ")spills the parts into neighbouring cells and updates if A2 changes.=TEXTSPLIT(A2, ", ")handles a comma-and-space separator in one go. - SPLIT (Google Sheets):
=SPLIT(A2, " ")does the same; wrap it in ARRAYFORMULA to split a whole column.
Splitting names is never perfect: "Mary Ann Smith" and "Jean-Luc de Vries" don't follow a first-and-last pattern, so check rows with more parts than expected. =COUNTA(TEXTSPLIT(A2, " ")) gives the number of parts per row, and any row above 2 needs a look by hand.
Checking the combined column
- Double spaces:
=COUNTIF(F2:F500, "* *")counts results with two spaces in a row — a sign that & joined an empty cell. - Leading or trailing spaces: compare
=SUMPRODUCT(LEN(F2:F500))with the same sum overTRIM(F2:F500); any difference means stray spaces. - Missing parts:
=COUNTIF(F2:F500, "")should be zero if every row has at least one value to combine. - Spot check: sort by the combined column and look at the first and last few rows, where odd values tend to collect.
Combining columns from two different files
Everything above joins cells that are already on the same row. If first names are in one file and surnames or email addresses in another, you first need to match the rows on a shared ID, otherwise each name is joined to someone else's details. Do that with a lookup or a join, then combine the columns. See joining two spreadsheets on a common column and the wider guide to joining spreadsheets.
Every guide in this topic
- How to combine text from multiple cells in Excel
Combine text from two or more cells in Excel: &, CONCAT and TEXTJOIN, separators and line breaks, skipping blanks, formatted numbers, and joining by condition.
- How to combine two columns in Excel
Combine two columns in Excel into one without losing data: the & operator, CONCAT, TEXTJOIN, Flash Fill and Power Query Merge Columns, plus dates and numbers.
- How to concatenate in Google Sheets
Combine text in Google Sheets with &, CONCATENATE, CONCAT, TEXTJOIN and JOIN: differences, separators, skipping blanks and filling a whole column.
- How to merge and unmerge cells with VBA
Merge cells with VBA using Range.Merge and MergeCells, keep every value instead of only the top-left, and unmerge and fill a whole sheet in a loop.
- How to merge cells in Excel
Merge cells in Excel with Merge & Center, Merge Across or Merge Cells, unmerge them, find merged cells, and keep every value by combining the text first.
- How to merge cells in Google Sheets
Merge cells in Google Sheets horizontally, vertically or all at once, unmerge them, and combine the contents of cells without losing any data.
- How to use Center Across Selection in Excel
Centre a heading over columns without merging cells. Exact steps, a keyboard shortcut workaround, the Google Sheets equivalent and why it beats Merge.
- How to use Merge & Center in Excel
Merge and Center in Excel with the Alt+H+M+C shortcut, fix it when it's greyed out, avoid losing data and use the safer Center Across Selection.
- Merged cells break sorting and filtering: unmerge and fill down
Why merged cells stop Excel sorting, hide rows from filters and break combines, and how to unmerge and fill down the values safely in a few steps.
Frequently asked questions
- How do I merge cells in Excel without losing data?
- Combine the values first with =TEXTJOIN(" ",TRUE,A1:C1), paste the result as values, then merge or use Center Across Selection. Merge & Center alone keeps only the upper-left value.
- How do I combine two columns in Excel?
- In a new column, enter =A2&" "&B2 (or TEXTJOIN), fill it down, then copy and Paste Special → Values.
- Why can't I sort a range with merged cells?
- Excel needs every row in a sort range to have the same cell structure. Unmerge the cells, or use Center Across Selection for headings.
- What's the difference between CONCAT and TEXTJOIN?
- CONCAT joins values with nothing in between unless you add separators yourself. TEXTJOIN adds a delimiter between every value and can skip empty cells.
- How do I unmerge every merged cell on a sheet?
- Click the corner button to select the whole sheet, then choose Home → Merge & Center → Unmerge Cells. Fill the gaps it leaves with Go To Special → Blanks, = and the up arrow, then Ctrl+Enter.
- How do I add a line break when combining cells?
- Use CHAR(10) as the separator, for example =TEXTJOIN(CHAR(10), TRUE, A2:C2), and turn on Wrap Text for the cell.