The fastest way to split cells in Excel is to use the Text to Columns wizard (Data tab > Data Tools > Text to Columns) for data separated by commas, spaces, or tabs. For more dynamic results that update automatically, use the TEXTSPLIT function (Microsoft 365) or the Flash Fill shortcut (Ctrl+E on Windows, Cmd+E on Mac). This article explains each method, when to use it, and how to handle common pitfalls like merged cells and formulas.
Why Splitting Cells Is Different From Splitting Text
In Excel, a “cell” is a single box that can hold up to 32,767 characters. When people say “split cells,” they usually mean one of two things: splitting the text inside a cell into multiple columns (e.g., “John Smith” into “John” and “Smith”), or splitting a merged cell back into individual cells. The first is a data transformation; the second is a formatting change. This article focuses on splitting text, because that is the far more common task. If you literally want to split a merged cell, select it, go to Home > Merge & Center, and click it again to unmerge.
Method 1: Text to Columns (Best for One-Time Cleanup)
Text to Columns is the oldest and most reliable method. It works in every Excel version, including Excel for the web (with some limitations). It splits data based on a delimiter (like a comma) or a fixed width. Here is how to use it:
- Select the column that contains the data you want to split. For example, assume A1:A10 has full names like “Jane Doe”.
- Go to Data tab > Data Tools > Text to Columns.
- Choose Delimited (if your data uses commas, tabs, or spaces) or Fixed width (if you want to split at a specific character position).
- If Delimited, check the delimiter(s) you need—comma, tab, semicolon, space, or a custom one. You can preview the result at the bottom.
- Click Next, choose the destination cell (default is the original column, which will overwrite it—so consider copying the data first), then click Finish.
Important: Text to Columns overwrites the original data. If you want to keep the original, copy the column to a new location first, or set the destination to a different column (e.g., $B$1). Also, if you have a header row, select only the data rows, not the header, unless you want the header split too.
Method 2: TEXTSPLIT Function (Dynamic and Auto-Updating)
If you have Microsoft 365 or Excel 2024, the TEXTSPLIT function is the modern way. It splits text into rows or columns based on a delimiter, and the result updates automatically if the source cell changes. The syntax is:
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [pad_with])
For example, with the full name in A2, put this in B2:
=TEXTSPLIT(A2, " ")
This splits on each space, so “Jane Ann Doe” would spill into B2, C2, and D2 as “Jane”, “Ann”, “Doe”. If you only want the first and last name, you can combine with TAKE or use TEXTSPLIT with a comma delimiter if your data is “Doe, Jane”. Note that TEXTSPLIT returns a dynamic array that spills into adjacent cells. If anything blocks those cells, you will see a #SPILL! error. Clear the blocking cells, and it will work.
Method 3: Flash Fill (Fastest for Simple Patterns)
Flash Fill (Ctrl+E on Windows, Cmd+E on Mac) is a smart fill that recognizes patterns. It is not a formula, so the result is static, but it is incredibly fast for splitting data like extracting first names or splitting “Product-123” into two columns. Here is how:
- In a new column next to your data, type the first value you want (e.g., type “Jane” in B2, next to A2 which has “Jane Doe”).
- Press Ctrl+E (Windows) or Cmd+E (Mac). Excel will fill the rest of the column based on the pattern.
- If the pattern is inconsistent, Flash Fill may not work perfectly—check the results and correct any errors manually.
Flash Fill is available in Excel 2013 and later, including Excel for the web. It works best when your data follows a clear, repetitive pattern. For example, if A2 is “John Smith”, typing “John” in B2 and pressing Ctrl+E will fill B3 with “Mary” from “Mary Johnson”, and so on.
Method 4: Using Formulas (LEFT, RIGHT, MID, and FIND)
When you need to split text into a fixed number of columns and you want the result to be formulas (so they update when the source changes), you can use text functions. This is the classic approach that works in all versions. For example, to split “John Smith” in A2 into first and last name:
- First name:
=LEFT(A2, FIND(" ", A2) - 1)→ returns “John” - Last name:
=RIGHT(A2, LEN(A2) - FIND(" ", A2))→ returns “Smith”
If the data has a comma (e.g., “Smith, John”), you can use FIND to locate the comma and then extract. For more complex splits, consider using TRIM and SUBSTITUTE to clean up extra spaces. These formulas are static in the sense that they recalculate when the source changes, but they do not spill like TEXTSPLIT—you must drag them down.
How to Choose the Right Method
Your choice depends on your version, whether you need the result to update, and how messy your data is:
- Text to Columns: Best for one-time cleanup, works everywhere, handles multiple delimiters, but overwrites data and is not dynamic.
- TEXTSPLIT: Best for Microsoft 365 users who want a dynamic, formula-based result that spills automatically. Cannot be used in older Excel versions.
- Flash Fill: Best for quick, pattern-based splits without formulas. It is static, so if the source changes, you must re-run it.
- Formulas (LEFT/RIGHT/MID): Best when you need a formula-based result in older Excel versions, or when you want to combine splitting with other calculations.
Common Problems and How to Fix Them
Here are the most frequent issues people run into when splitting cells:
- Data appears in one column after Text to Columns: You likely forgot to select the delimiter, or the data uses a delimiter that is not checked. Go back and check the correct delimiter (e.g., semicolon for European CSV files).
- #SPILL! error with TEXTSPLIT: Something is blocking the spill range. Select the cell with the formula, go to Home > Editing > Clear > Clear All on the blocking cells, or move the formula to an empty area.
- Flash Fill does nothing: Make sure you have a blank column to the right. Flash Fill only works when there is a clear pattern and a blank column. Also, ensure you are pressing Ctrl+E (not just typing).
- Merged cells prevent splitting: If you have merged cells, Text to Columns will not work. Unmerge all cells first (Home > Merge & Center > Unmerge Cells).
- Leading/trailing spaces: If your split results have extra spaces, wrap your formula with
TRIM(), or use Text to Columns and then apply TRIM in a helper column.
Frequently Asked Questions
Can I split a cell into rows instead of columns? Yes. Text to Columns always splits into columns. To split into rows, you can use TEXTSPLIT with a row_delimiter argument. For example, =TEXTSPLIT(A1, , ",") splits on commas and spills into rows. In older Excel, you would need to use a combination of TRANSPOSE and TRIM with MID and SEARCH, which is complex. For a one-time conversion, copy the column, paste it into a Word document, replace commas with paragraph marks, and paste back—but that is clunky.
Does Text to Columns work on Excel for Mac? Yes, the feature exists in Excel for Mac, but the menu path is slightly different: Data tab > Text to Columns (it is under the Data tab, not in a separate Data Tools group). The rest of the steps are the same.
How do I split a cell with a line break (Alt+Enter)? Use Text to Columns, and in the Delimiters step, check the “Other” box and press Ctrl+J (Windows) or Cmd+J (Mac) to enter a line break as the delimiter. For TEXTSPLIT, use the CHAR(10) function as the delimiter: =TEXTSPLIT(A1, CHAR(10)).
Why does my TEXTSPLIT formula return #VALUE!? This usually happens if you use a delimiter that is not found in the text, or if you have a typo in the delimiter. Check that the delimiter is exactly correct (including spaces). Also, if you use an array of delimiters, ensure they are in the correct order.
Can I split cells without losing the original data? Yes. With Text to Columns, set the Destination to a different column (e.g., $B$1) before clicking Finish. With TEXTSPLIT, the original cell is never modified because the formula is in a separate cell. With Flash Fill, the original data stays intact because you are typing into a new column. Only the overwrite-by-default behavior of Text to Columns is risky, so always check the destination.
Safety and Data Accuracy Tips
Before splitting a large dataset, always make a backup copy of the workbook (File > Save As > copy). Text to Columns overwrites data irreversibly—there is no Undo if you close the file. Also, when using TEXTSPLIT, be aware that it is a volatile function in the sense that it recalculates when the source changes, but it is not volatile like RAND(). If you share the workbook with someone using an older Excel version, they will see a #NAME? error because TEXTSPLIT does not exist there. In that case, use Text to Columns instead. Finally, if your data contains formulas, splitting will convert them to text, so copy values first if you need to preserve formulas.
Related
- Excel Before Close Event: How to Run Code on Save
- SUBSTRING in Snowflake: Syntax, Examples, and Tips
- Excel Audit Process: Step-by-Step Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Group Edit Mode: How to Ungroup SheetsTo get out of group edit mode in Excel, right-click any selected worksheet tab and choose Ungroup Sheets.…
- Is Oracle Stock Going to Split? What Investors Should KnowNo—Oracle has not announced a forthcoming stock split, and there is no confirmed split date or share-conversion ratio.…
- How to Share a Personal Calendar in Outlook on Any DeviceTo share a personal calendar in Outlook, open Calendar, choose the calendar’s sharing option, add the recipient, select…
- Translate English to Spanish in Google Sheets EasilyTo translate English text into Spanish in Google Sheets, enter =GOOGLETRANSLATE(A2,"en","es") in an empty cell, replacing A2 with…
- Create a Fillable Template in Word: Step-by-StepTo create a fillable template in Word, turn on the Developer tab, add content controls such as text…
- Excel PivotTable Tips for Clearer ReportsFor better Excel PivotTables, start with a tidy, rectangular source table, then use Insert > PivotTable and place…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.