How to Split Cell Data in Excel: Text to Columns & Formulas

To split cell data in Excel, select the cells, choose Data > Text to Columns, pick Delimited or Fixed width, set the separator or break points, choose an empty destination, and select Finish. This creates separate columns from the original text. If you need a split that updates when the source changes, use a formula instead; Flash Fill is another option for straightforward patterns.

What You’ll Need

An Excel workbook with the text you want to separate, such as full names, addresses, or product codes.
For Text to Columns, enough empty columns to the right of your source data, or a different empty destination where Excel can put the results.
For the formula method, Microsoft 365 or Excel 2024 for the TEXTSPLIT function. Excel for the web also supports many Microsoft 365 functions, though commands and availability can vary.
A copy of the workbook or an untouched source column if the original text needs to be preserved. Text to Columns can replace existing cell contents.

Step-by-Step

Check the data and choose a splitting method. Look at several source cells before you start. If every entry separates at the same character, such as a comma, semicolon, or space, Text to Columns is usually the quickest option. Choose Fixed width when fields have consistent positions but no delimiter. Use a formula when the result should recalculate after you edit the source. Flash Fill can recognize a pattern from examples, but it is less dependable when the source format varies.
Keep a safe copy and select the source cells. If the original values matter, duplicate the worksheet or copy the source column to another location before splitting. Select the cells containing the text, including the header only if you want the header processed too. Avoid selecting entire rows or unrelated columns. If you select cells with inconsistent layouts, inspect the preview carefully because Excel applies the same delimiter rules to the entire selection.
Open the Text to Columns wizard. In Excel for Microsoft 365 on Windows, go to Data > Text to Columns. The Convert Text to Columns Wizard opens. Choose Delimited if a character marks each boundary—for example, commas in Jordan Lee,Portland. Choose Fixed width if each piece occupies a predictable position, such as a code whose first four characters always identify a department. Select Next.
Set the delimiter or field widths. For Delimited, check the separator used in your data: Tab, Semicolon, Comma, Space, or Other. Enter a character in Other when the delimiter is not listed. The preview shows where Excel will break the text. If fields are separated by more than one space, select Treat consecutive delimiters as one when appropriate. Leave it unchecked if each delimiter represents an empty field you need to keep. For Fixed width, click in the preview to add a break line, drag a line to adjust it, or double-click a line to remove it. Select Next when the preview matches your intended columns.
Choose a safe destination and finish. The wizard defaults to the first selected cell as the destination. That can overwrite the source and nearby cells. In the Destination box, enter or select an empty starting cell, such as D2, if you want the original data left in place. Confirm that enough blank columns are available for the split results, then select Finish. Review the output row by row, especially entries with missing separators or extra delimiters.
Use a formula when you want a dynamic split. With the text Jordan Lee,Portland in A2, enter the formula below in B2. It splits the value at the comma and spills the results into the next column: B2 displays Jordan Lee and C2 displays Portland. The formula assumes the delimiter is a comma and requires Microsoft 365 or Excel 2024.

=TEXTSPLIT(A2,",")

Make sure the cells to the right of the formula are empty so the result can spill. To use it on more records, fill the formula down the column or use an Excel table so formulas can extend as rows are added. The source value stays unchanged, and editing A2 updates the split result automatically. If the formula displays a spill error, check for content or merged cells in the spill area.

Try Flash Fill for a repeated example pattern. If you want to extract first names from a list of full names, type the first expected result next to the first source value. In the next row, begin typing the next result; Excel may show a preview. Accept the preview if it matches, or use Data > Flash Fill. On Windows, Ctrl+E runs Flash Fill when the relevant cells are selected. Check the output for exceptions: Flash Fill creates values from a pattern and does not keep them linked to the source. On Mac, use Data > Flash Fill if the command is available in your Excel version; keyboard shortcuts can differ.
Verify the split and format the results. Check a few ordinary rows and every unusual row. Confirm that names, dates, leading zeros, and blank fields came through as intended. Excel may interpret a piece such as 00127 as a number and remove its leading zeros. If those characters are meaningful, set the destination column to Text before using the wizard, or correct the column format after splitting. Save the workbook once the output is right.

Common Mistakes

Overwriting the source or adjacent data: Text to Columns writes results into neighboring cells unless you specify another destination. If those cells contain information, it may be replaced. Check the destination and clear space before selecting Finish.

Choosing the wrong separator: A comma-delimited list will not split correctly if you select Space, and a space delimiter can split a full name into more columns than intended. Match the delimiter to the actual data, not just one example. The preview is the easiest place to catch this.

Assuming every row follows the same pattern: A missing comma or an extra comma can shift fields into the wrong columns. Review irregular entries and consider cleaning them up first. If one field contains the delimiter as part of its content, a simple split may not be suitable without a consistent text format.

Expecting a permanent connection after splitting: Text to Columns and Flash Fill produce fixed results. Editing the original cell later does not automatically update those results. Use TEXTSPLIT in a supported Excel version when you want a formula-driven output.

Ignoring data types: Excel may convert text that looks like a date or number, remove leading zeros, or display long numbers in scientific notation. If the exact characters matter, treat that output as text rather than accepting an automatic conversion.

Tips

Make a small test split on a few representative rows before processing thousands of records. Include a normal entry, one with a missing value, and one with the most separators. This helps reveal whether consecutive delimiters should count as one or whether they mark empty fields.

Use the destination box to preserve the source during the first pass. Once you have checked the output, you can decide whether to keep both versions or replace the original column. Keeping a source column is especially useful when the split fields need correction later.

For a delimiter made of several characters, such as a slash followed by a space, Text to Columns may require a workaround because the wizard handles individual delimiter choices. You can replace the multi-character separator with a unique single character in a copy of the data, then split on that character. Choose a replacement that does not already occur in the values.

If your data comes from a CSV or another imported file, consider whether it is better to split it during import. Correctly identifying delimiters and text columns as you load the file can prevent leading zeros or date-like text from being converted unexpectedly.

Excel for Mac has Text to Columns and Flash Fill in supported desktop versions, though the ribbon layout and shortcuts may differ. Excel for the web can open and edit workbooks, but some ribbon commands or behaviors may not match desktop Excel. If a command is missing, open the workbook in the desktop app; a supported formula such as TEXTSPLIT may still be an option in Microsoft 365.

Troubleshooting

Nothing splits: Check that the selected delimiter exactly matches the character in the cell. A regular space, nonbreaking space, and tab can look similar but are different characters. If the content came from a web page, paste a few examples into a plain-text editor or replace the unusual separator in a copy, then try again.

The split lands in the wrong columns: Return to the wizard preview and check for extra delimiters inside a field. If consecutive delimiters represent a single boundary, enable Treat consecutive delimiters as one. If they represent a missing value, leave that option off. For Fixed width, adjust the break lines until each field appears in the correct preview column.

Some output disappears: Undo immediately with Ctrl+Z on Windows (Command+Z on Mac) if you just finished and the result overwrote data. If you have made later edits, undo may reverse those too, so use the preserved source or workbook copy when possible. Before running Text to Columns again, set a destination in a blank area and check that it has enough space.

TEXTSPLIT shows #SPILL!: Clear the cells where the result needs to appear, including any invisible-looking spaces or formulas. Also check for merged cells in that area. The dynamic result cannot expand into occupied or merged cells.

Flash Fill gives inconsistent results: Provide more examples that make the intended pattern unambiguous, then run Flash Fill again. If records follow different naming or punctuation rules, use Text to Columns for a clear delimiter or a formula designed for the specific layout. Flash Fill is pattern-based and may guess incorrectly on exceptions.

Leading zeros or dates change: Undo if possible, then repeat the split with the relevant destination column formatted as Text before conversion. When a value has already been converted to a number or date, formatting it as Text afterward may not restore the original characters; use the untouched source to recover them.

Frequently Asked Questions

Can I split one cell into multiple rows instead of columns?
Text to Columns is designed to place split pieces in separate columns. To put the pieces in rows, split them into columns first and then copy and paste them transposed, or use a suitable dynamic-array formula in a supported Excel version. Check the result before replacing the source.

Can I split text using a space?
Yes. In the Text to Columns wizard, choose Delimited and check Space. Be aware that this splits at every space, so a full name such as Jordan Lee becomes two fields. If you only need to separate the first word from the rest, use a method suited to that pattern rather than splitting on every space.

Does Text to Columns update when I change the original cell?
No. It writes the split pieces as values. If the source changes, run the wizard again or use a formula such as TEXTSPLIT in Microsoft 365 or Excel 2024 to keep the output linked.

How do I split data without losing the original?
Set an empty destination in the Text to Columns wizard before you select Finish, or duplicate the source column first. With a formula, place the formula in a different column; the source cell remains intact.

Why is Text to Columns unavailable or missing?
Check that you are using a desktop Excel version and that the Data tab is visible. Excel for the web and different Mac releases can show commands differently. If the command is not available in your current interface, use the desktop app or a supported formula-based method.

Can I split a column by a line break?
Line breaks may not appear as a selectable delimiter in every version of the wizard. If the preview does not split at them, replace line breaks in a copy of the data with a unique character, then split on that character. Confirm the replacement character does not occur elsewhere in the text.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.