Analistable

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

Text-joining options in Excel and Google Sheets
FormulaResult for A2=Ada, B2=LovelaceAvailable in
=A2&" "&B2Ada LovelaceAll versions, Google Sheets
=CONCATENATE(A2," ",B2)Ada LovelaceAll versions, Google Sheets
=CONCAT(A2," ",B2)Ada LovelaceExcel 2019+, 365 (Sheets: two values only)
=TEXTJOIN(" ",TRUE,A2:B2)Ada LovelaceExcel 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

  1. Fill the formula down the helper column.
  2. Copy the column, then Paste Special → Values (Ctrl+Alt+V, then V).
  3. 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

Combining columns with different formulas
FirstMiddleLastFormulaResult
AdaLovelace=A2&" "&B2&" "&C2Ada Lovelace (double space)
AdaLovelace=TEXTJOIN(" ",TRUE,A2:C2)Ada Lovelace
GraceBrewsterHopper=TEXTJOIN(" ",TRUE,A3:C3)Grace Brewster Hopper
GraceBrewsterHopper=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.

Excel's merge commands
CommandWhat it doesWindows keys
Merge & CenterMerges the whole selection into one cell and centres the textAlt, H, M, C
Merge AcrossMerges each selected row separately, keeping the rows apartAlt, H, M, A
Merge CellsMerges the selection without changing alignmentAlt, H, M, M
Unmerge CellsSplits merged cells back into separate cellsAlt, 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:

  1. 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.
  2. Choose Home → Merge & Center → Unmerge Cells. The value stays in the top cell of each former merge and the cells below are empty.
  3. With the column still selected, choose Home → Find & Select → Go To Special → Blanks.
  4. 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.
  5. 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:

TEXT inside a combined value (A2 = 15/03/2026, B2 = 1250.5, C2 = 742, D2 = 0.125)
FormulaResult
="Date: "&A2Date: 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

Address columns
NameLine 1Line 2TownPostcode
Cedar Books12 Mill LaneYorkYO1 7HU
Bloom FloristUnit 4Station YardLeedsLS1 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

  1. Select a cell in the data and choose Data → From Table/Range to open the Power Query editor.
  2. Ctrl-click the columns in the order you want them joined.
  3. Choose Transform → Merge Columns to replace them with one column, or Add Column → Merge Columns to keep the originals.
  4. Pick a separator (space, comma, tab or a custom one) and name the new column.
  5. 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

Which combining features each version has
FeatureExcel 2016Excel 2019 / 2021Microsoft 365Google Sheets
& and CONCATENATEYesYesYesYes
CONCAT and TEXTJOINNoYesYesYes (CONCAT takes two values)
TEXTSPLITNoNoYesUse SPLIT
Flash FillYesYesYesSmart Fill suggestions instead
Center Across SelectionYesYesYesNo

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

Problems when merging cells or combining text
SymptomLikely causeFix
#NAME? with TEXTJOIN or CONCATExcel 2016 or olderUse & or CONCATENATE
The formula shows as text instead of a resultThe cell was formatted as Text before typingSet it to General, then press F2 and Enter
A date appears as a five-digit numberThe date was joined without TEXTWrap it: TEXT(A2,"dd/mm/yyyy")
Leading zeros have goneThe code is stored as a numberUse TEXT(A2,"00000"), or store codes as text
Sorting fails with a message about merged cellsThe range contains merged cells of different sizesUnmerge, fill the blanks, then sort
Line breaks don't showWrap Text is offTurn on Wrap Text for the result column
Results turn to #REF! after deleting columnsThe formulas still referred to the deleted columnsPaste as values before deleting
Error about the number of arguments in Google SheetsCONCAT given more than two valuesUse CONCATENATE, TEXTJOIN or &

Center Across Selection step by step

  1. Type the heading in the leftmost cell, for example A1, and leave B1:F1 empty.
  2. Select A1:F1.
  3. Open Format Cells with Ctrl+1 on Windows or Cmd+1 on a Mac.
  4. 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 over TRIM(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

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.