To split cell data into separate columns in Excel for Microsoft 365 on Windows, select the cells, choose Data > Text to Columns, select Delimited or Fixed width, and follow the prompts. For a formula-based split that updates when the original text changes, use TEXTSPLIT in Microsoft 365 or Excel 2024.
Choose the right way to split a cell
The best method depends on whether you need a one-time cleanup or a result that stays linked to its source. Text to Columns is a practical choice for a fixed list of names, codes, or addresses. It replaces the selected source values with separate columns, so plan where the results will go before you run it. TEXTSPLIT returns a formula result and can update when the source changes. Flash Fill is useful when you can show Excel a clear example of the pattern. Power Query is a better fit for repeatable imports or larger data-cleaning workflows.
For example, a cell containing Jordan Lee, Seattle, WA could become three fields: a name, a city, and a state. If your source is Jordan Lee, however, splitting on every space produces two name parts; that is not necessarily the same as separating first and last names in a dataset that includes middle names or multiword surnames. Decide what each output column should mean before choosing a delimiter.
- Use Text to Columns for a quick, one-time split in the worksheet.
- Use TEXTSPLIT when you want formula results that recalculate from the source.
- Use Flash Fill for recognizable patterns that are awkward to describe with one delimiter.
- Use Power Query when you regularly import and transform the same kind of data.
Split data with Text to Columns
Text to Columns is Excel’s built-in wizard for dividing one column into multiple columns. First, make room for the output: if neighboring columns contain information, insert blank columns or choose an empty destination. The wizard can overwrite existing cells to the right of the source, and Excel may warn you before doing so.
- Select the cells in the column you want to split. A heading can be excluded by selecting only the data rows.
- On the ribbon, choose Data > Text to Columns.
- Choose Delimited if a character such as a comma, tab, semicolon, or space separates the pieces. Choose Fixed width if the pieces align at consistent character positions.
- For a delimited split, select the delimiter or enter a custom one if the wizard offers that field. Check the preview to see how Excel will divide each row.
- Choose a destination if you do not want the results to start in the original column, and set column formats where needed. Select Finish.
Suppose A2 contains 1027,Green,Notebook, with corresponding records below it. Choosing the comma delimiter produces three columns: 1027, Green, and Notebook. A tab-separated export can be divided by selecting Tab instead. The preview is worth checking: a delimiter that appears inside a value can create more columns than expected.
With Fixed width, click in the preview to add a break line where one field ends and another begins; drag a line to adjust it or remove it if it is misplaced. This works well for fixed-position reports, but not for ordinary text where field lengths vary. If the first name is sometimes four characters and sometimes nine, a break at the same character position will not reliably separate names.
Use TEXTSPLIT for a formula that updates
TEXTSPLIT is available in Microsoft 365 and Excel 2024. It separates text using a column delimiter, a row delimiter, or both. With the source text in A2, enter the formula in an empty cell with enough blank space to its right:
With the text 1027,Green,Notebook in A2, this formula splits at each comma and returns three values across the row: 1027, Green, and Notebook.
=TEXTSPLIT(A2,",")
The result is a spilled array: Excel fills adjacent cells automatically. Leave that spill area empty, or Excel will show a #SPILL! error. If you edit A2, the formula result updates. Copy the formula down beside other records to split multiple rows; each row’s output can spill across the required number of columns.
If comma-space is the actual separator, specify both characters so that the spaces do not remain at the start of the next field. With Jordan Lee, Seattle, WA in A2, the formula below returns Jordan Lee, Seattle, and WA.
=TEXTSPLIT(A2,", ")
TEXTSPLIT can also handle repeated delimiters. By default, consecutive delimiters can create empty results. If A2 contains red,,blue and you want to ignore the empty position between the commas, use the optional fourth argument:
With red,,blue in A2, this formula ignores empty results and returns red and blue in adjacent columns.
=TEXTSPLIT(A2,",",,TRUE)
The empty third argument leaves the row delimiter unspecified; TRUE is the ignore-empty setting. If a delimiter appears only between certain fields, consider whether ignoring empty positions is correct: an empty field may carry real meaning, such as a missing middle initial. TEXTSPLIT is not available in older perpetual Excel versions, where Text to Columns or Power Query may be more suitable.
Split with Flash Fill
Flash Fill can extract or rearrange parts of text by learning from examples. For instance, if A2 contains Jordan Lee, type Jordan in B2. Start entering the first name from A3 in B3; Excel may display a preview of the remaining first names. Accept the suggestion with Enter, or choose Data > Flash Fill. You can repeat the process in another column to extract last names.
Flash Fill is convenient when the desired result follows a pattern but is not defined by a simple separator. It creates values rather than formulas, so changes to the source do not automatically update the extracted results. Review the output carefully when names, punctuation, or formats vary. A pattern that works for most rows can still produce a wrong result for a row with a middle name or an unusual entry.
Use Power Query for repeatable imports
Power Query is Excel’s data import and transformation tool. It is useful when you receive the same kind of text file or report repeatedly and want to apply the same splitting steps after each refresh. In the Power Query Editor, select the source column and use Split Column to split by a delimiter or by positions. The available options depend on the selected split method and Excel build; common choices include splitting at each occurrence or at the left-most or right-most delimiter.
To start with a worksheet table, select a cell in the table and choose Data > From Table/Range. In the editor, select the column, choose Split Column, configure the split, and then use Home > Close & Load to return the result to Excel. The transformation steps are saved with the query. When the source data is replaced or updated in the expected format, refresh the query to run the steps again. Power Query is more setup than a one-time split, but it avoids repeating manual cleanup each time.
Preserve codes, dates, and other important values
A split can change how Excel interprets a value. For example, a code such as 00741 may become the number 741 if Excel converts it to numeric data. If leading zeros are meaningful, set the destination column’s format to Text in the Text to Columns wizard before finishing, or format an empty destination column as Text before entering formula results. Formatting a number as Text after zeros have already been removed does not restore them.
Dates can also be misread when imported text uses a different regional order. A value such as 04/05/2026 could mean April 5 or May 4. In Text to Columns, use the column data format options to specify a date order when appropriate, and verify the result against the source’s intended convention. Do not assume that a date-looking string has been interpreted correctly just because Excel displays it as a date.
When fields contain a delimiter as part of their own text, a plain delimiter split may divide the field incorrectly. For example, comma-separated data can include a quoted value such as "Seattle, WA". Check how the source file is structured and inspect the wizard preview. For recurring CSV imports with quoted fields, importing through Power Query is often more reliable than manually splitting a pasted line of text.
Common problems and how to fix them
- Existing data gets replaced: undo immediately with Ctrl+Z on Windows, or Command+Z on Mac, if the operation has just completed. Then insert blank columns or set a safe destination and try again.
- TEXTSPLIT shows #SPILL!: clear the cells where the result needs to appear. Merged cells or other content in the spill range can block the output.
- Some rows split into a different number of columns: look for missing delimiters, extra delimiters, or delimiters inside a field. Decide how blanks and unusual records should be handled before applying the split to all rows.
- Spaces remain around the results: choose the exact delimiter, such as comma plus space, or clean the source data. A split at the comma alone leaves the following space with the next field.
- Leading zeros disappear or dates change: set an appropriate text or date format before conversion and compare representative results with the original values.
- Text to Columns is unavailable or looks different: check that you are using the Excel desktop application and the Data tab. Ribbon details can vary by platform or update; in Excel for the web, use TEXTSPLIT if it is available in your account, or open the workbook in the desktop app for the wizard.
Text to Columns is available in Excel for Mac, though ribbon layout and keyboard behavior can differ from Windows. The menu path may appear under the Data tab, and the dialog can look different. The instructions above use Microsoft 365 for Windows. Windows shortcuts should not be assumed to work on Mac; use the ribbon command if a shortcut differs. In either app, save a copy before a large conversion if the original layout must be preserved.
Frequently Asked Questions
Can Excel split one cell into multiple columns automatically?
Yes. Use Data > Text to Columns for a one-time split, or TEXTSPLIT in Microsoft 365 or Excel 2024 for a formula result that updates with its source. Choose based on whether you need a fixed conversion or a live formula.
How do I split a full name into first and last names?
If each name has exactly two parts separated by one space, Text to Columns with Space selected can separate them. For real-world lists with middle names, multiple-word surnames, or single names, a simple space split may not identify first and last names correctly; review those records or use a rule that matches your data.
Can I split text into rows instead of columns?
TEXTSPLIT supports a row delimiter as its third argument, so a formula can return separated items down rows. Text to Columns is designed to divide selected text across columns; Power Query also provides options for splitting into rows, depending on the split settings.
Will Text to Columns change the original data?
It writes the split results into the worksheet and can replace the original column or overwrite cells to its right. Choose a different destination or make a copy first if you need to retain the unsplit text. TEXTSPLIT leaves the source cell in place, but its output needs empty adjacent cells.
Why is TEXTSPLIT not recognized in my Excel?
TEXTSPLIT requires Microsoft 365 or Excel 2024. Older versions may not include it. Use the Text to Columns wizard for a one-time split, or Power Query for a repeatable import transformation.
Related
- Excel Remove Slicer: How to Delete or Clear One
- Excel Remove Everything but Duplicates: Keep Repeats
- Excel Remove Duplicates Not Available? Fixes to Try
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Power Query: Enable Background RefreshTo enable background refresh in Excel for Microsoft 365 on Windows, open Data > Queries & Connections, right-click…
- Excel Cell Selection: Ranges, Lists, and ShortcutsTo select a list of cells in Excel, press Ctrl+G, type the cell references separated by commas—for example,…
- Merge Cells in Excel Using Keyboard Shortcuts on WindowsTo merge cells in Excel for Microsoft 365 on Windows using only the keyboard, select the range and…
- Archive Outlook Emails to an External Hard DriveYes. In classic Outlook for Microsoft 365 on Windows, export email to an Outlook Data File (.pst), then…
- Excel Flash Fill in Polish: Name and How to Use the FeatureIn Polish-language Excel, Flash Fill is called Wypełnianie błyskawiczne. In Microsoft 365 for Windows, find it at Data…
- Track Changes in Word: How to Review and Accept EditsTo use Track Changes in Microsoft Word for Microsoft 365 on Windows, open the document, select Review >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.