To split delimited text in Excel for Microsoft 365 on Windows, select the cells, choose Data > Text to Columns, select Delimited, choose the separator, set the output format and destination, then click Finish. A delimiter is a character such as a comma, tab, semicolon, or space that marks where one piece of text ends and the next begins.
What Delimited Text to Columns Does
Text to Columns takes text stored in one column and separates it into two or more columns. For example, a cell containing Ravi Patel,Operations,Seattle can become three cells: Ravi Patel, Operations, and Seattle. Excel repeats the split for each selected row, using the delimiter or delimiters you specify.
The command is useful when information has arrived in a single field from a copied web page, a report, a text file, or an export. It does not infer what each piece means. You decide which characters mark the boundaries, and you can choose how Excel interprets the resulting pieces.
The wizard also offers a Fixed width option. That is different from Delimited: fixed-width data is split at positions you place in a preview, rather than at a recurring character. Use Delimited when a consistent separator is present, even if the pieces have different lengths.
Check the Data Before Splitting
Before running the command, inspect a few rows and confirm that the separator really distinguishes fields. A comma-separated list is not always as simple as it looks: an address or name might itself contain a comma. Quotes can protect such text, but the file needs to use them consistently for Excel to interpret the fields as intended.
Also check the columns to the right of the source. Text to Columns writes its results horizontally, so splitting column A into three fields uses columns A, B, and C. Existing data in those cells may be replaced or prompt you to confirm the change. To keep both the original text and the split result, set a different destination in the wizard, such as B2, and make sure there is enough empty space to the right.
- Save a copy of important workbooks before a large conversion.
- Remove merged cells in the output area if they would obstruct the results.
- Check whether values such as product codes, postal codes, or long account numbers need to remain text.
- Decide whether blank fields between separators should be retained or whether consecutive separators should count as one.
Run the Text to Columns Wizard
- Select the cells in the single column that contains the text. You can select the whole column, but selecting just the populated range makes the operation easier to review.
- On the ribbon, choose Data > Text to Columns. The Convert Text to Columns Wizard opens.
- On the first screen, choose Delimited, then click Next. Choose Fixed width instead only when the split positions are consistent from row to row.
- On the delimiter screen, select the relevant separator and check the data preview. Excel offers Tab, Semicolon, Comma, Space, and Other. For Other, type the character in the small box beside the option.
- Use the preview to verify that each field lands in the intended column. If the lines do not divide correctly, change the delimiter or inspect the source for inconsistent punctuation.
- Click Next to set the format for each resulting column and, if needed, choose a destination cell. Then click Finish and check several rows of the result.
In desktop Excel for Mac, the command is also available from the Data tab as Text to Columns, though the wizard’s appearance and ribbon layout can vary slightly by release. These steps describe desktop Excel; if the command is not available in your version of Excel for the web, open the workbook in the desktop app to use the wizard.
Choose the Right Delimiter
A delimiter is a boundary marker, not necessarily punctuation. The right choice depends on how the source was created. In a typical tab-separated report, fields are separated by tabs even if they look like ordinary spaces on screen. In a comma-separated file, commas divide the fields, while semicolons are common in some exports and regional settings.
Select every separator that is meant to divide a field, but do not select a character merely because it appears in the data. If a space is selected, for example, the value North Bay will be split into North and Bay. That may be appropriate for separate words, but it is usually wrong if the whole place name belongs in one field.
When several separators appear together, the Treat consecutive delimiters as one option can help. If it is unchecked, two consecutive separators can indicate an empty field between them. If it is checked, Excel treats the run as a single boundary. Consider Ravi,,Seattle: with comma selected and consecutive delimiters treated as one, Excel may place Ravi and Seattle in adjacent output fields; without that setting, the empty middle field can be preserved. Check the preview because the correct choice depends on what the empty position means in the source.
The Text qualifier setting matters when a delimiter occurs inside a quoted field. For example, if a record contains Patel,"Ravi, Jr.",Sales, the double quote qualifier tells Excel that the comma inside the quoted name is part of that field, not a separator. This works only when the source uses consistent quotation marks. If the preview splits the quoted text incorrectly, check the qualifier and the original file’s quoting pattern.
Set Formats to Preserve Values
The third wizard screen lets you select each output column and choose a data format. General is convenient for ordinary values because Excel interprets numbers and dates, but automatic interpretation can change data that only looks numeric. For example, a code such as 00427 may become 427 if Excel treats it as a number.
Choose Text for identifiers whose exact characters matter, including codes with leading zeros, some postal codes, and long digit strings. Excel can preserve only 15 significant digits in a numeric value; digits beyond that limit may be changed. Formatting a column as Text before it is interpreted helps keep such a value intact. If the value has already been converted and digits lost, changing the cell format afterward cannot restore them; return to the source text and import or split it again with the correct format.
For dates, select the column and choose Date, then pick the order that matches the source, such as MDY, DMY, or YMD. The order is important when a value like 03/04/2026 could mean March 4 or April 3. Do not assume that the date display on screen reveals how the original text was interpreted. If dates must remain exactly as written rather than become usable Excel dates, choose Text instead.
When imported numbers use decimal or thousands separators that differ from your Excel settings, the wizard’s Advanced option lets you specify those separators for the conversion. For example, a source may represent one thousand and a half as 1.000,5, while another uses 1,000.5. Match the source’s conventions rather than relying on appearance alone.
Worked Examples and Useful Checks
Suppose cells A2:A4 contain 4821|Mira Chen|West, 4822|Leo Ortiz|East, and 4823|Nia Shah|Central. Select A2:A4, open Data > Text to Columns, choose Delimited, select Other, and enter a vertical bar (|). The preview should show three fields per row: an ID, a name, and a region. Set the ID column to Text if its exact form must be kept, choose a destination if column B already contains data, and finish the conversion.
For a tab-delimited list, select Tab rather than Space. A tab can separate a full name from a department while leaving the space inside the name untouched. For a file where each field is separated by a semicolon, select Semicolon and make sure Comma is not also selected unless commas are intended to divide fields.
After splitting, verify the first, middle, and last rows, not only the preview. Look for shifted values, unexpected blank columns, lost zeros, dates interpreted in the wrong order, and text that was cut off into extra columns. A quick comparison of the number of source rows and resulting rows can also reveal whether records were omitted or whether the operation was applied to the intended range.
Common Problems and How to Fix Them
Everything stays in one column: The selected delimiter may not match the source. Try Tab or Other, and inspect the preview. A space that looks like a separator might actually be a tab, or the source may use a character you did not select.
Names or addresses split in the wrong place: Avoid using a space as a delimiter when values contain internal spaces. If a field contains the chosen delimiter, use a consistent text qualifier when available, or choose another method that lets you control the split more precisely.
Zeros disappear or dates change: Set the affected output column to Text, or choose the correct Date order, before clicking Finish. Formatting the result after conversion may change how a value displays but cannot reliably restore characters already lost through numeric conversion.
Results overwrite nearby information: Undo immediately with Ctrl+Z on Windows or Command+Z on Mac if the conversion has just occurred and the workbook is still in the same undo history. Then run the wizard again with an empty destination range. Do not rely on undo as a backup for complex work; save a copy first.
Rows split inconsistently: The source may contain mixed separators, missing fields, or inconsistent quoting. Review representative rows and clean or standardize the source before splitting. A single wizard setting cannot reliably infer every irregular pattern.
Text to Columns Compared with Other Methods
Text to Columns is a good choice for a one-time, visible conversion: it turns the selected text into ordinary cell values and does not leave a formula behind. Its main limitation is that the result does not automatically update if the original text changes. You also need to manage the destination columns and choose formats carefully.
For repeatable imports, Data > From Text/CSV can be more suitable in desktop Excel. It opens a preview for a text or CSV file and provides import options, including delimiter and data-type choices; loading through Power Query can make the transformation refreshable when the source file is updated. The exact options depend on the file and Excel version. This is often preferable for recurring reports, while Text to Columns is convenient when the text is already in worksheet cells.
In Microsoft 365 and Excel 2024, the TEXTSPLIT function can split a value with a formula and return results across cells. With the original text in A2 and a comma as the separator, use =TEXTSPLIT(A2,","). If A2 contains Mira,Chen,Sales, the result spills into three cells: Mira, Chen, and Sales. The formula can recalculate when A2 changes, but it requires empty cells for its results and depends on a version that supports the function. For a quick, permanent split, the wizard is usually more straightforward.
Frequently Asked Questions
Can I split text into columns using a space?
Yes. In the wizard, select Space as the delimiter. Use it only if spaces mark field boundaries; otherwise, names and phrases containing spaces will be divided into extra columns.
How do I split a cell at a comma without losing the original?
In the wizard’s final screen, set Destination to an empty cell in another column, such as B2, before clicking Finish. Confirm there is enough unused space to the right for every resulting field.
Why does Excel remove leading zeros when I split text?
Excel may interpret a numeric-looking field as a number, and numeric values do not retain leading zeros. Select that output column in the wizard and choose Text before finishing the split.
Can Text to Columns split data into rows instead?
No. The wizard separates each selected row’s text horizontally into columns. To turn separated items into rows, use a different transformation, such as Power Query or a suitable formula, depending on the data and Excel version.
Does Text to Columns change the original column?
By default, the split starts at the selected source range, so the original text is replaced by the first output field. Choose a different destination in the wizard if you need to retain the original text.
Related
- Consolidate in Excel: Example, Steps, and Common Issues
- Consolidate Data in Excel Across Multiple Worksheets
- Consolidate Excel Sheets with Power Query: A Practical 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 File Name Extensions: .xlsx, .xlsm, .xls and MoreThe standard Excel file name extension is .xlsx, the default for workbooks in Microsoft Excel for Microsoft 365…
- Test Excel Data: Practical Checks for Accuracy and ErrorsTo test Excel data, check for missing values, duplicates, invalid entries, and formulas that do not match the…
- How to Create Small Labels in Microsoft Word EasilyTo create small labels in Word, open Mailings > Labels, choose a label product that matches your sheet,…
- How to Calculate Age in Excel From a Date of BirthTo calculate age in completed years in Excel, put the date of birth in A2 and enter =DATEDIF(A2,TODAY(),"Y")…
- Add Page Numbers in Word: Steps, Sections, and FixesIn Microsoft Word for Microsoft 365 on Windows, select Insert > Page Number, choose where the number should…
- How to Create Plain Labels in Word Without a TemplateTo create a plain sheet of blank labels in Microsoft Word, open Mailings > Labels, choose the product…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.