To split text in Excel, select the cells and choose Data > Text to Columns to separate values with a delimiter such as a comma or space. For a formula that updates when the source changes, use TEXTSPLIT in Microsoft 365 or Excel 2024; the best method depends on whether you need a one-time split, a repeatable result, or a split down rows instead of across columns.
What splitting text in Excel means
Splitting text means taking one cell’s contents and dividing them into separate cells. For example, a cell containing Riley Morgan, Support, Seattle could become three cells: a name, a department, and a city. Excel needs a rule for where to make each cut. That rule is usually a delimiter, such as a comma, tab, hyphen, slash, or space.
Splitting can also mean separating a value at a particular position, such as taking the first five characters of a product code, or separating a list into multiple rows. Those are different jobs, and the right tool depends on the structure of the text:
- Delimiter-based split: Divide text wherever a known character appears, such as a comma or semicolon.
- Fixed-width split: Divide text at specified character positions, useful when each field has a consistent length.
- Pattern-based split: Extract parts that follow a rule, such as text before the first space or after a colon.
- Split into rows: Place each separated item on a new row rather than in the next column.
A split usually creates separate values, not a permanent connection between the new cells and the original. Text to Columns changes the selected data, while a formula creates results that recalculate from the source. Keep an untouched copy of important source data before using a destructive or bulk transformation.
Split text with Text to Columns
Text to Columns is a practical choice for a one-time cleanup in desktop Excel for Microsoft 365 on Windows. It works well when a whole column follows the same delimiter rule.
- Make sure the columns to the right of your source are empty, or choose a different destination in the wizard. The results can overwrite existing data.
- Select the cells to split, then choose Data > Text to Columns.
- Choose Delimited if a character separates the fields, or Fixed width if the split points are at consistent positions. Select Next.
- For a delimited split, check the delimiter you need: Tab, Semicolon, Comma, Space, or Other. For example, choose Other and type a vertical bar if the data uses
|. - Check the preview. Set a text qualifier if fields are enclosed in quotation marks, and use the option to treat consecutive delimiters as one only if that matches your data.
- Select Next, set the destination or column data format if needed, and choose Finish.
For example, if A2 contains Riley Morgan, Support, Seattle, choosing comma as the delimiter puts the three fields in adjacent columns. The wizard can also split a space-delimited list, but that would separate Riley from Morgan as well as separating the fields. That is why a delimiter must be chosen with the actual data structure in mind.
Text to Columns can convert values as it splits them. If a field contains an identifier such as 00127, set that output column’s format to Text in the wizard to preserve the leading zeros. Dates can also be interpreted according to a selected format; inspect the preview and resulting values if the source uses a date order that differs from your regional settings. On Mac, look for Data > Text to Columns; ribbon details can vary by Excel version. If the command is unavailable in Excel for the web, use a formula or open the workbook in the desktop app.
Use TEXTSPLIT for an updating result
TEXTSPLIT is useful when the source may change and you want Excel to recalculate the separated fields. It is available in Microsoft 365 and Excel 2024. In the formula below, A2 contains Riley,Morgan,Seattle; enter the formula in B2, and the result is Riley in B2, Morgan in C2, and Seattle in D2.
With the source text in A2, separated by commas, enter this in B2:
=TEXTSPLIT(A2,",")
The formula returns a dynamic array that spills into neighboring cells. Leave the spill area clear. If Excel shows #SPILL!, a value or merged cell may be blocking the intended output. Editing A2 updates the split results automatically, but typing into one of the spilled result cells is not allowed while the formula controls that range.
To split text into rows, use the row-delimiter argument. With a line-break-separated list in A2, enter the following in B2; the items spill down from B2 rather than across columns:
=TEXTSPLIT(A2,,CHAR(10))
Here the second argument is left empty because there is no column delimiter, and CHAR(10) represents a line break in a cell. The source must actually contain line breaks for this split to produce separate items.
TEXTSPLIT also has options for consecutive delimiters and matching. For example, if A2 contains Riley,,Seattle and you want to ignore the empty field between the two commas, enter this in B2:
=TEXTSPLIT(A2,",",,TRUE)
The result is Riley in B2 and Seattle in C2. Without the fourth argument set to TRUE, Excel preserves the empty field, so Seattle appears in D2 instead. Preserving empty fields is often important when a blank represents a missing value in a structured record.
Extract a specific part with formulas
Sometimes a full split is unnecessary. If you need only the text before or after a separator, TEXTBEFORE and TEXTAFTER can make the intention clearer. These functions are available in Microsoft 365 and Excel 2024.
With a full name in A2, such as Riley Morgan, enter this in B2 to return Riley:
=TEXTBEFORE(A2," ")
With the same value in A2, enter this in C2 to return Morgan:
=TEXTAFTER(A2," ")
These examples split at the first space. They do not correctly separate every possible full name into first and last names: a middle name or a surname containing spaces creates additional parts. For an address or code with several components, splitting on a delimiter may be more suitable. If a delimiter might be absent, consider how you want the formula to behave rather than assuming every row has identical text.
Older Excel versions without these newer functions can use Text to Columns, Flash Fill, or combinations of functions such as LEFT, MID, RIGHT, and FIND. Those formulas can work, but they need more care when the delimiter appears more than once or is missing. If a workbook must be shared with older Excel versions, check that the recipient’s version supports every function used; otherwise, a formula may display as an unsupported-function error.
Use Flash Fill for recognizable patterns
Flash Fill can fill a column when Excel recognizes a pattern from examples you type. It is helpful for irregular text that does not have a simple, consistent delimiter, such as extracting the first name from a list of names with varied spacing. It creates values rather than a formula that stays linked to the source, so later edits to the original text do not automatically update the filled results.
- Put the source values in one column and type the desired result for the first row in the adjacent column.
- Type a second example if the pattern may not be obvious from the first.
- Select a cell in the results column and choose Data > Flash Fill, or press Ctrl+E on Windows.
- Review the generated entries, especially rows that differ from the examples.
Flash Fill is pattern recognition, not a strict parsing rule. It may infer the wrong result when a column mixes formats, such as names with and without middle initials. On Mac, use Data > Flash Fill if available in your Excel version; keyboard shortcuts can vary with the system and Excel release. For repeatable data imports, a formula or Power Query transformation is generally easier to audit.
Split columns with Power Query
Power Query is a stronger option for recurring cleanup, larger tables, or a process that should be repeated after new data arrives. It records transformation steps, so you can refresh the query when the source changes instead of repeating each manual split.
In desktop Excel, select a cell in the source table and choose Data > From Table/Range. In the Power Query editor, select the column, then choose Transform > Split Column > By Delimiter. Choose the delimiter and specify whether to split at each occurrence or at a particular occurrence, then confirm the transformation. When the data looks right, choose Home > Close & Load to return the results to Excel.
Power Query is especially useful when a monthly file arrives with the same layout, or when the source needs several steps such as trimming spaces, changing data types, and splitting a field. It is more setup than Text to Columns for a single small task. The exact buttons can differ slightly between Windows, Mac, and web versions, so use the Power Query options available in your installation. Keep the original source or query connection if you expect to refresh the result.
Choose the right split method
There is no single best way to split every cell. Consider whether the split should be permanent, update automatically, or run again as new data arrives.
- Choose Text to Columns for a fast, one-time split of a consistently formatted column.
- Choose TEXTSPLIT when you want formula-driven results and have Microsoft 365 or Excel 2024.
- Choose TEXTBEFORE or TEXTAFTER when you need only the part on one side of a delimiter.
- Choose Flash Fill when the desired result follows an example-based pattern that is awkward to express as a simple delimiter rule.
- Choose Power Query when you need a repeatable data-cleaning process that can be refreshed.
Also decide whether your output belongs in columns or rows. Columns are convenient for records with a fixed set of fields, such as city, state, and postal code. Rows are often more useful for a list of tags or products that will be counted or filtered individually. A split that looks tidy is not automatically the best structure for later analysis: a separate row for each item can be easier to summarize than a long list kept in one cell.
Common problems and how to fix them
- Everything stays in one column: Check that you selected the delimiter actually used in the source. A comma and a semicolon are different characters; a tab may look like a space in a preview.
- Values split into too many pieces: A chosen character may also appear inside a field. For example, splitting names on spaces separates both first and last names, and a comma inside quoted text may not be a true field boundary.
- Some fields are blank or shifted: Look for consecutive delimiters, trailing delimiters, or missing values. Decide whether to preserve empty fields or ignore them before applying a formula.
- Leading zeros disappear: Excel may treat the output as a number. Use the Text format in the Text to Columns wizard, or format the destination appropriately before importing or splitting.
- A formula returns an error: Confirm that the Excel version supports the function and that the delimiter exists in the source. A dynamic array can also return
#SPILL!if cells in its output area are occupied. - Results overwrite other data: Text to Columns writes into adjacent cells. Undo immediately with Ctrl+Z on Windows, or restore from a saved copy if the workbook has since been changed.
One frequent misconception is that splitting a cell automatically preserves the meaning of quoted fields. If a comma-separated record contains a field such as "Morgan, Riley", a simple comma split may separate that quoted value incorrectly. Use the Text to Columns text-qualifier setting when the file uses quotes properly, or import and inspect the data with Power Query. Always verify representative rows, including the longest, shortest, and least typical records, before relying on a bulk result.
Frequently Asked Questions
How do I split text in Excel by a space?
Select the cells and choose Data > Text to Columns, select Delimited, and check Space. In Microsoft 365 or Excel 2024, a formula such as =TEXTSPLIT(A2," ") splits the value in A2 at spaces and spills the pieces across columns.
Can Excel split text into rows instead of columns?
Yes. With Microsoft 365 or Excel 2024, use the row-delimiter argument in TEXTSPLIT; for line breaks in A2, =TEXTSPLIT(A2,,CHAR(10)) returns the items down the rows. Power Query can also split a column into rows.
How do I split a cell at the first delimiter only?
If you need the text before or after the first delimiter, use TEXTBEFORE or TEXTAFTER in Microsoft 365 or Excel 2024. Text to Columns and a basic TEXTSPLIT formula generally split at every occurrence of the selected delimiter.
Why does TEXTSPLIT show #SPILL?
The formula needs empty cells for its results. Clear the cells where the array should appear, unmerge any cells in that area, and check that the formula is not inside an Excel table where dynamic-array spilling is restricted.
Does splitting text change the original cell?
Text to Columns writes results over the selected cells and adjacent destinations, so preserve a copy if the source matters. A formula leaves the source cell intact, while Flash Fill produces separate values that do not automatically recalculate from later source edits.
Related
- Excel Mail Merge: Create Letters, Labels, and Emails
- Excel Daylight Saving Time: How Dates and Hours Work
- Monthly Excel Budget Format: Build a Simple Template
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Calculate Age in Excel as of Today: Formulas That WorkTo calculate age in completed years as of today, put each person’s date of birth in A2 and…
- Count Excel Rows After a Filter: Formulas That WorkTo count rows currently visible after filtering, use SUBTOTAL on a column that has a value in every…
- COUNTIFS in Excel: Exact and Partial Text MatchesYes. In Excel, COUNTIFS can count cells that match text exactly or contain a text fragment: use =COUNTIFS(A2:A100,"Apple")…
- Excel Difference Between Two Numbers: Formula GuideTo find the signed difference between two numbers in Excel, subtract one cell from the other, such as…
- Excel Trim a Table: Clean Text or Remove Rows?To trim text inside an Excel table, add a column with =TRIM([@[Raw Name]]); to trim the table’s physical…
- Excel QR Code Formula: Create QR Codes from Cell DataExcel has no built-in QR-code function, but in Microsoft 365 for Windows you can display a QR code…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.