Yes. In Excel for Microsoft 365 on Windows, select the cells and choose Data > Text to Columns to split delimited or fixed-width text into separate columns. For a formula that updates when the source changes, use TEXTSPLIT: with the text in A2, enter =TEXTSPLIT(A2,", ") in B2 to split at each comma followed by a space.
The right method depends on whether you need a one-time cleanup, a result that recalculates, or a repeatable import process. Before splitting, make room for the output: Excel can place results in neighboring columns, and existing data there may be overwritten.
Choose a method for the job
Excel offers several ways to divide text, but they solve slightly different problems. A comma-separated list, a full name, and a fixed-position product code may look similar in one cell while needing different treatment.
- Text to Columns: best for a quick, one-time split using a delimiter such as a comma, space, tab, or a fixed character position.
- TEXTSPLIT: best when you want a formula result that updates as the original text changes. It is available in Microsoft 365 and Excel 2024.
- Flash Fill: useful when Excel can infer a pattern from examples, such as extracting first names or area codes.
- Power Query: a good choice for repeated imports or larger datasets that need the same cleanup each time.
For a single column of consistently formatted entries, start with Text to Columns. If new source values will arrive regularly, consider a formula or Power Query instead. Splitting is not the same as moving information into multiple cells by hand: each method has different behavior when the original data changes.
Split text with Text to Columns
Text to Columns is Excel’s built-in wizard for turning one column of text into several. It works on selected cells and writes the results into adjacent columns. In Excel for Microsoft 365 on Windows, use these steps:
- Select the cells you want to split. If you select a whole column, make sure there is room for the results to its right.
- Choose Data > Text to Columns.
- Select Delimited if a character separates the pieces, or Fixed width if each piece occupies a predictable position. Select Next.
- For a delimited split, choose the delimiter, such as Tab, Semicolon, Comma, Space, or Other. The preview shows how Excel will divide the text. Select Next.
- Choose a column data format if needed, set the destination cell if you do not want the output to begin in the original column, and select Finish.
For example, a cell containing Riley Chen, West, 18 can be split on commas to produce three fields: Riley Chen, West, and 18. If spaces inside names are not intended as separators, do not select Space as the delimiter; use the comma instead. The preview is a useful check before committing the split.
When the source contains consecutive delimiters, the wizard’s option to treat consecutive delimiters as one can prevent empty columns. The text qualifier setting can also matter when fields are surrounded by quotation marks, as in "Chen, Riley", West. In a properly quoted CSV-style row, the comma inside the name should not separate the name into two fields. Check the preview and qualifier setting rather than assuming every comma is a field boundary.
Use TEXTSPLIT for an updating result
TEXTSPLIT divides text using one or more delimiter arguments and returns the pieces into neighboring cells as a dynamic array. Unlike Text to Columns, the formula result can recalculate when the source cell changes. It requires Microsoft 365 or Excel 2024; it is not available in every older Excel release.
With the text Lee, Jordan, 42 in A2 and the output area beginning in B2, enter =TEXTSPLIT(A2,", "). The result is Lee in B2, Jordan in C2, and 42 in D2. The delimiter here is the exact two-character sequence comma and space. If the source uses commas without spaces, use a comma as the delimiter instead.
The returned values spill into neighboring cells automatically. Keep those cells empty, or Excel may show a #SPILL! error because something blocks the result. A formula can also behave unexpectedly if the delimiter is inconsistent: splitting at comma-space will not match a row that contains a comma with no following space. Standardize the source or choose a delimiter that matches its actual pattern.
TEXTSPLIT also supports a row delimiter, which can place separated pieces down rows instead of across columns. It can handle empty results and specify a replacement for missing values, but those options are only useful when the source structure calls for them. For a simple split, start with the text and column delimiter arguments and test the formula on representative entries before filling or relying on it for a full dataset.
When Flash Fill is a better fit
Flash Fill can recognize a pattern from examples and fill corresponding values without requiring a delimiter formula. It is useful when the desired output is based on meaning or a consistent sample rather than a simple character boundary. For instance, if column A contains Jordan Lee, type Jordan in the first cell of column B and Lee in the first cell of column C. After entering a second example if needed, choose Data > Flash Fill or press Ctrl+E on Windows.
Check the results carefully. Flash Fill infers a pattern; it does not create a live formula relationship with each source cell. If the source changes later, the extracted values do not automatically update. It can also make a wrong inference when names or codes follow inconsistent formats. On Mac, use the Flash Fill command on the Data tab if the Windows shortcut does not apply in your setup.
Use Power Query for repeatable splits
Power Query is helpful when you regularly import a file or need to apply the same transformation to many rows. It records steps that can be refreshed, so a recurring task does not have to be repeated manually for each new import. In Excel for Microsoft 365 on Windows, select a cell in the data table and choose Data > From Table/Range. If prompted, confirm that the range has headers when appropriate.
In the Power Query Editor, select the column, then choose Home > Split Column > By Delimiter or use the corresponding command on the Transform tab. Set the delimiter and choose whether to split at each occurrence or at a specified occurrence, then confirm. When finished, select Home > Close & Load to return the transformed data to Excel.
Power Query is more setup than a one-time Text to Columns operation, but it can save effort when the same source format returns. It also lets you review transformations as steps. Remember that a refreshed query updates its loaded output; it does not necessarily overwrite or edit the original source file.
Delimiters, fixed widths, and data formats
A delimiter is a character or sequence that marks where one value ends and the next begins. Common delimiters include commas, tabs, semicolons, pipes, and spaces. The best delimiter is one that separates fields but does not occur inside the values themselves. A pipe, for example, may be safer than a space when people’s names contain spaces.
Fixed width is different: it splits text at positions rather than looking for a character. It can work for a consistently formatted code such as AB1047NY if the first two characters always identify a category, the next four a number, and the last two a region. In the wizard preview, set the break points at the correct positions. If the source varies in length, fixed-width breaks can divide values incorrectly.
Think about data types before finishing. Excel may interpret a field as a number or date, which can change its appearance or value. A postal code such as 00421 can lose its leading zeros if treated as a number. In the Text to Columns wizard, select the relevant output column and choose Text when the characters must be preserved exactly. Dates can also be interpreted according to a selected date order, so check that an entry such as 03/04/2026 is read as intended.
Common problems and how to avoid them
- Output overwrites nearby data: choose an empty destination column in the wizard or move existing data before splitting. Make a copy first if the source is important.
- Every word becomes a separate column: this often happens when Space is selected for data that contains spaces within a field. Use a more specific delimiter.
- Extra blank columns appear: repeated delimiters or leading and trailing delimiters may create empty fields. Inspect the source and use the consecutive-delimiter option where appropriate.
- Leading zeros disappear or dates change: Excel has converted text to another data type. Set the output format to Text for identifiers, and verify date interpretation before finishing.
- A TEXTSPLIT formula shows #SPILL!: clear the cells where the results need to appear. A merged cell in the spill range can also prevent the array from expanding.
- Rows split differently: the input may not use one consistent delimiter. Compare several examples before choosing a method; a formula that matches one row may not correctly parse another.
For a one-time split, pressing Ctrl+Z on Windows or Command+Z on Mac can undo the operation immediately. Do not rely on undo as a backup plan for a complex cleanup; keep the original column or save a copy first. Splitting changes the layout of data and can be difficult to reverse after further edits.
Frequently Asked Questions
How do I split one Excel cell into multiple columns?
Select the cell or column and choose Data > Text to Columns. Choose Delimited, select the separator, check the preview, and finish. For an updating formula, use TEXTSPLIT in Microsoft 365 or Excel 2024.
Can Excel split text automatically when I type it?
Text to Columns is a manual operation, and Flash Fill generates values from a pattern rather than continuously recalculating them. TEXTSPLIT can update when its source changes, provided the formula and spill area remain in place.
How do I split text at a space but keep first and last names together?
A simple space delimiter will split both the boundary between first and last names and any spaces inside multi-part names. If the data has another reliable separator, use it. Otherwise, use a carefully checked pattern with Flash Fill or design a more specific formula for the actual name format.
Why does Text to Columns put data in the wrong places?
The selected delimiter may appear inside values, or the rows may use inconsistent spacing or punctuation. Review the wizard preview, try a more specific delimiter, and confirm the destination does not overlap other data.
Can I split a column in Excel for the web?
Feature availability can vary in Excel for the web. If Text to Columns is not available in your ribbon, use TEXTSPLIT if supported in your workbook, or open the file in the Microsoft 365 desktop app and use Data > Text to Columns.
Does splitting a column change the original text?
Text to Columns writes the split pieces into the selected destination area and may replace the original column’s contents. A TEXTSPLIT formula leaves the source cell intact and displays results elsewhere. Keep a copy when the original values need to be preserved.
Related
- Excel Not Blank Symbol: What <> Means
- Choose an Excel Data Range: Methods, Tips, and Shortcuts
- Excel Letter Count: Count Letters and Characters in Cells
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Analysis ToolPak: Setup and Uses“Excel data analysis pack” usually means the Analysis ToolPak, a Microsoft Excel add-in for statistical and engineering calculations.…
- How to Open a Corrupted Excel File Online and Recover DataTo try opening a corrupted Excel file online, first save an untouched copy, upload a duplicate to OneDrive,…
- Google Sheets Dropdown Formatting: Colors and RulesTo format a dropdown in Google Sheets, edit its data-validation rule and assign colors to its options; to…
- Excel Cell Phone Accessories in Clementi: What to KnowIf you mean a shop called Excel that sells cell phone accessories in Clementi, I can’t reliably confirm…
- Percentage of a Number in Excel: How to Calculate ItTo find a percentage of a number in Excel, multiply the number by the percentage. For example, with…
- How to Change Pane View in OutlookTo change the pane view in Outlook, open View, choose Layout if your version shows it, then select…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.