Split Combine Merge Excel: How To Do It

Updated 6 November 2026 · Standard formula, shown below

This article explains the different ways to split, combine, and merge data in Microsoft Excel, covering both built-in features and formula-based approaches. You will learn how to split text into columns, combine text from multiple cells, merge entire sheets or workbooks, and handle common problems like data loss during merging. The guide focuses on current mainstream Excel versions (Microsoft 365, Excel 2021, 2019, and 2016) and gives step-by-step instructions you can follow immediately.

Background: Why Splitting and Merging Is Tricky

Excel is built around a grid of cells, but real-world data rarely arrives in perfect columns. You might get a full name in one cell (“John Smith”), a date in text format (“2023-04-15”), or a list of email addresses separated by commas. Splitting means breaking one cell’s content into multiple cells. Combining (or concatenating) means joining text from several cells into one. Merging usually refers to combining entire ranges, sheets, or workbooks, which is a different operation from merging cells (which simply joins adjacent cells visually).

The confusion often comes from the word “merge” because Excel has a Merge & Center button that does something completely different—it combines cells into one larger cell, keeping only the upper-left value. That is rarely what people want when they ask to “merge data.” This article clarifies the distinction and shows the correct tools for each task.

How to Split Text in Excel

The fastest way to split text is the Text to Columns wizard, available on the Data tab. It works for delimited text (commas, tabs, semicolons, spaces) and fixed-width text.

Select the column that contains the text you want to split.
Go to Data > Text to Columns.
Choose Delimited (if your text uses separators) or Fixed width (if you want to split at specific character positions).
For delimited, check the delimiter (comma, tab, semicolon, space, or other). You can preview the result at the bottom.
Click Finish. The original column is replaced by the split columns (unless you specify a different destination).

For example, if cell A1 contains “John,Smith,NY”, Text to Columns with a comma delimiter will place “John” in A1, “Smith” in B1, and “NY” in C1. This is a one-time operation—it does not update if the original data changes.

If you need a dynamic formula that updates automatically, use the newer TEXTSPLIT function (available in Microsoft 365 and Excel 2021). For example, =TEXTSPLIT(A1,",") returns a horizontal array of the split parts. You can also use TRANSPOSE(TEXTSPLIT(A1,",")) to get a vertical list.

How to Combine Text from Multiple Cells

To join text from two or more cells, you have two main options: the ampersand (&) operator or the CONCAT function (which replaced the older CONCATENATE function in Excel 2016).

Ampersand method: =A1 & " " & B1 gives you the contents of A1, a space, and B1.
CONCAT function: =CONCAT(A1, " ", B1) does the same. CONCAT can also take a range, like =CONCAT(A1:A5), which joins all cells in that range with no separator.
TEXTJOIN function (Microsoft 365, Excel 2019+): =TEXTJOIN(", ", TRUE, A1:A5) joins the range with a comma and space, ignoring empty cells. This is the most flexible option.

For example, if A1 contains “John” and B1 contains “Smith”, the formula =A1 & " " & B1 returns “John Smith”. The result is a text string, and you can copy it as a value if you don’t want the formula to recalculate.

How to Merge Cells vs. Merge Data

Excel’s Merge & Center button (on the Home tab, in the Alignment group) combines selected cells into one cell. It keeps only the upper-left value and discards the rest. This is useful for creating a header that spans multiple columns, but it is not for combining data—you will lose information.

To merge data without losing anything, use the CONCAT or TEXTJOIN formulas described above, or use the Flash Fill feature (Excel 2013 and later). Flash Fill can detect a pattern: type the desired result in the first row, press Ctrl+E, and Excel fills the rest. For example, if you type “John Smith” in C1 and press Ctrl+E, Excel will combine first and last names for all rows. Flash Fill works best when the pattern is consistent.

How to Merge Two Excel Sheets or Workbooks

Combining data from multiple sheets or workbooks is a common task. There are several approaches, depending on whether you want a one-time copy or a dynamic connection.

Copy and paste: The simplest method. Open both workbooks, select the data, copy (Ctrl+C), switch to the destination sheet, and paste (Ctrl+V). This works for small datasets but is manual and error-prone for large ones.
Power Query (Get & Transform): The most powerful tool. Go to Data > Get Data > From File > From Workbook (or From Folder if you have many files). Load each sheet as a query, then use Append Queries to stack rows on top of each other, or Merge Queries to join columns based on a key. This creates a refreshable connection that updates when the source files change.
Consolidate feature: For numeric data that you want to sum or average across sheets, use Data > Consolidate. This lets you combine ranges by category or position. It works well for summary reports but not for raw data.

For example, if you have two sheets with sales data (same columns), you can use Power Query to append them into one table. If you have customer info in one sheet and orders in another, you can merge them on a Customer ID column.

Common Problems and Mistakes

Several issues frequently trip up users:

Data loss when merging cells: As mentioned, Merge & Center deletes all but the upper-left value. Always use a formula or Power Query to combine data.
Leading zeros disappear: When splitting text that includes numbers like “00123”, Excel may convert them to numbers and drop the zeros. To preserve them, format the destination columns as Text before splitting, or use the Text Import Wizard.
Dates become serial numbers: When combining text with dates, Excel might display the date’s underlying serial number (e.g., 45000). Use the TEXT function to format the date, like =A1 & " " & TEXT(B1,"mm/dd/yyyy").
Separator issues: When using CONCAT, remember that it does not add separators automatically. You must include them as arguments. TEXTJOIN is easier because it lets you specify a delimiter.
Formulas vs. values: If you copy a formula that references other cells, the result may change when the source changes. To freeze the result, copy the cells and paste as values (right-click > Paste Special > Values).

Choosing the Right Method

Which method you choose depends on your goal:

One-time cleanup: Text to Columns for splitting, and CONCAT/TEXTJOIN for combining.
Dynamic updates: Use formulas (TEXTSPLIT, TEXTJOIN) so results recalculate when source data changes.
Combining large datasets: Use Power Query for appending or merging tables. It handles millions of rows and refreshes with one click.
Creating a summary: Use Consolidate for numeric summaries across sheets.
Visual headers: Use Merge & Center, but only for display purposes, not for data storage.

Frequently Asked Questions

Why does Merge & Center only keep the upper-left value?

That is how Excel is designed. Merging cells is meant for layout, not data storage. The upper-left value is kept because Excel assumes you want to display a single piece of text across multiple cells. To combine data, use CONCAT or TEXTJOIN instead.

Can I split a cell into rows instead of columns?

Yes. Use the TEXTSPLIT function with the row_delimiter argument, or use Text to Columns and then transpose the result. For example, =TEXTSPLIT(A1,",") splits into columns; =TEXTSPLIT(A1,","," ") can split into both rows and columns.

How do I merge two Excel files without copying and pasting?

Use Power Query. Go to Data > Get Data > From File > From Workbook, select the first file, load it as a query, then repeat for the second file. Then use Append Queries to stack them. This creates a live connection that you can refresh.

What is the difference between CONCAT and CONCATENATE?

CONCATENATE is the older function, available in Excel 2013 and earlier. CONCAT was introduced in Excel 2016 and is more flexible—it can take a range of cells as an argument, while CONCATENATE only accepts individual cell references. Both join text, but CONCAT is recommended for newer versions.

Can I split text by multiple delimiters at once?

Yes. In Text to Columns, you can check multiple delimiters (e.g., comma and space). In TEXTSPLIT, you can specify multiple delimiters using an array constant, like =TEXTSPLIT(A1,{",",";"}).

Why does my merged data show #VALUE! error?

This usually happens when you try to combine text with a number using the & operator, but the number is actually a date or time stored as text. Use the TEXT function to convert it properly, or check for hidden characters like line breaks. Also, make sure you are not referencing an entire column in a CONCAT formula, which can cause issues.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.