In Microsoft Excel for Microsoft 365 on Windows, find the Text to Columns button on the Data tab, in the Data Tools group. Select the column you want to split, choose Data > Text to Columns, then follow the wizard to split text by a delimiter such as a comma or by fixed-width positions.
Where to find the Text to Columns button
Open the workbook in the Excel desktop app and select a cell in the column containing the text. On the ribbon, choose Data > Text to Columns. The command is in the Data Tools group, though its exact position can shift if the Excel window is narrow or the ribbon is customized. Expanding the ribbon or making the window wider can make the group easier to locate.
Text to Columns is a command on the Data tab, not a formula or a separate worksheet function. Clicking it opens a multi-step wizard. The wizard previews how Excel will divide the selected data before you apply the change, so you can inspect the proposed split and adjust settings first.
In Excel for Mac, look on the Data tab for Text to Columns; ribbon layout can vary by release. Excel for the web may arrange commands differently or offer a different set of features. If you cannot find the command in the web ribbon, open the workbook in the desktop app and use its Data tab.
What the command does
Text to Columns takes text from one column and distributes its parts across neighboring columns. It is useful when several values have been stored together in a single cell, such as a first and last name separated by a space, or a sequence of fields separated by commas. It changes the worksheet data; it does not simply change how a value looks.
For example, if A2 contains Riley Chen and you choose a space as the delimiter, Excel can place Riley in one column and Chen in the next. If A2 contains Chen, Riley, West and comma is the delimiter, Excel can create three fields. The source column is divided at each matching separator, so the result depends on the actual pattern in the data.
The command is not a general-purpose way to split every kind of information. It works best when records follow a consistent structure. If one row has a middle name, an extra comma, or a missing field while others do not, the split may put different kinds of information in the same output column.
How to use the wizard
- Select the cells in the source column, or click a cell in that column. Check that there are no unrelated values immediately to the right of the source data.
- Choose Data > Text to Columns. In the first step, select Delimited when a character separates the fields, or Fixed width when fields line up at consistent character positions. Click Next.
- For Delimited, select the separator used in the text: common choices include Tab, Semicolon, Comma, and Space. Choose Other to enter another character. Review the preview, then adjust the delimiter or text qualifier if necessary.
- For Fixed width, inspect the preview and click where a field should end to add a column break. Drag a break to reposition it, or double-click one to remove it. Use the preview to check that the breaks line up across the records.
- Click Next to reach the format options. Choose General, Text, or a date format as appropriate for each output column. Set Destination to a blank area if you want to keep the original data.
- Check the preview one last time, then click Finish. Inspect several resulting rows, especially records that have unusual spacing, punctuation, or missing values.
You do not have to select every cell in the column before opening the wizard, but selecting the intended range makes it clearer what will be processed. If Excel detects a continuous block of data, verify the preview and resulting rows rather than assuming it included exactly the cells you intended.
Delimited or fixed width: choosing a split method
Choose Delimited when a visible character marks the boundary between fields. A comma-separated list, a tab-separated export, and text with fields separated by semicolons are typical cases. The delimiter should be chosen based on the actual data, not simply on what seems conventional.
Choose Fixed width when each field occupies a consistent number of character positions and there is no dependable separator. For instance, a plain-text report might put an account code in characters 1–8 and a description after character 9. Fixed-width splitting is less reliable if values vary in length or the apparent alignment comes from a font or layout rather than consistent text positions.
- Comma: suitable for values such as
Orchid,12,North, provided commas do not also appear inside fields in a way the text qualifier cannot handle. - Tab: often appropriate for text copied from a tabular report or exported as tab-separated data.
- Space: can split a full name into parts, but repeated spaces and names with different numbers of words need careful review.
- Other: useful for separators such as a pipe character in
Item|Qty|Region.
The Treat consecutive delimiters as one option can help when several spaces or other separators in a row should count as a single boundary. Do not enable it if empty fields are meaningful. For example, in a comma-separated record such as North,,Active, the two commas may indicate that the middle field is intentionally blank.
Protecting the source and preserving data
Text to Columns writes its results into worksheet cells. If the destination is the original source column, the first output field occupies that column and additional fields go into columns to its right. Existing data in those output cells can be replaced, so make sure the destination area is empty or choose a different starting cell in the wizard. Keeping an untouched copy of the source is a sensible precaution when the data is important.
Excel may warn you when the output would overwrite existing cells. Treat that warning as a prompt to check the destination rather than clicking through automatically. Also consider whether there is enough room to the right: a three-part split needs three output columns, not just the original one.
Pay special attention to identifiers and dates. A value such as 00127 may become the number 127 if Excel interprets it as General, dropping the leading zeros. If those zeros are part of an ID, set that output column to Text in the wizard. Likewise, a date such as 03/04/2025 can be interpreted differently depending on regional date conventions. Select a suitable date format when the fields are dates and verify the result.
Text qualifiers matter when a delimiter can appear inside a field. In a comma-separated record, a value might be enclosed in quotation marks to keep its internal comma together. Check the wizard’s text qualifier setting and preview for such records; choosing the wrong setting can split a field that was meant to remain intact.
Common problems and how to fix them
- Everything stays in one column: Confirm that the selected delimiter matches the character in the source. A file that looks separated by spaces may actually use tabs. Try selecting Other and entering the separator you can see.
- Words split too many times: A space delimiter separates at every space, not just the first one. A full name with a middle name may therefore produce three parts. Review the preview and consider whether a formula or a different cleanup method is more suitable.
- Leading zeros disappear: Set the relevant output column to Text before finishing, or restore the source and split again with the correct format. Changing a number’s display format afterward may not recover zeros that Excel has already removed.
- Results overwrite other data: Undo immediately with Ctrl+Z on Windows, then run the wizard again with a blank destination. On Mac, use Command+Z. Undo is most dependable before making other workbook changes.
- Some rows split differently: Look for inconsistent separators, quoted text, extra spaces, or missing fields. The wizard follows the delimiter or break settings; it cannot infer that differently structured records should mean the same thing.
One common misconception is that choosing a comma means Excel will split only at the first comma. It generally treats each selected delimiter as a field boundary throughout the text, producing additional columns for later commas too. If the number or position of delimiters varies by row, inspect the preview and results rather than relying on a single sample.
Text to Columns compared with other methods
Text to Columns is a practical choice for a one-time, relatively straightforward split. It is interactive, provides a preview, and allows column formats to be specified during the operation. Its main limitation is that it changes cells in place or writes to a destination; it is not a live transformation that automatically updates whenever the source text changes.
For repeatable cleanup of files that are imported regularly, Power Query can be a better fit. It can split a column by delimiter or by position as part of a query, and the transformation can be refreshed when the source data is updated. This adds setup and is unnecessary for a quick, one-off task, but it is useful when the same steps must be applied consistently to new data.
Formulas are useful when the split should update as the source value changes. In Microsoft 365 and Excel 2024, TEXTSPLIT can split text into cells using specified row or column delimiters. In older Excel releases, formulas can still be used, but the approach depends on the pattern and may be more involved. Unlike Text to Columns, formula results remain linked to the source and can recalculate when it changes.
For a small number of rows, manual editing may be simplest. For large, regularly updated datasets, consider Power Query or a formula-based method. The right choice depends on whether the split is a one-time edit, needs to refresh, or must preserve a connection to the original text.
Frequently Asked Questions
Where is Text to Columns in Excel?
In Excel for Microsoft 365 on Windows, select the source column and choose Data > Text to Columns on the ribbon. The command is in the Data Tools group.
How do I split a cell by a comma?
Select the column, open Data > Text to Columns, choose Delimited, select Comma, review the preview, set a safe destination if needed, and click Finish.
Can Text to Columns split names into first and last names?
Yes, if the names use a consistent space separator. However, names with middle names, multiple surnames, or inconsistent spacing may produce more than two fields, so inspect the output before relying on it.
How do I stop Excel from removing leading zeros?
In the wizard’s format step, select the output column and choose Text before finishing. This helps preserve identifiers such as postal codes or account numbers as text rather than numeric values.
Can I undo Text to Columns?
Usually, press Ctrl+Z on Windows or Command+Z on Mac immediately after applying it. If you have made other changes since, undo may reverse those first; keeping a copy of the original data is safer for important work.
Check the result before sharing
After splitting, compare several output rows with their original entries. Confirm that the number of fields is consistent, blank fields remain intentional, and dates or codes have not changed meaning. If the split is correct, you can keep the original column for reference or remove it after verifying the new columns. This final check catches the subtle errors a clean-looking preview can miss.
Related
- Excel Remove Duplicates While Keeping the Oldest Date
- Excel Remove Slicer: How to Delete or Clear One
- Excel Remove Everything but Duplicates: Keep Repeats
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Share a Calendar in New Outlook: Step-by-StepTo share a calendar in New Outlook, open Calendar, select the calendar you own, choose Share Calendar, enter…
- Excel Won’t Group Columns? Causes and FixesIf Excel will not group columns, select the column headers you want to fold, then choose Data >…
- How to Number Lines in Excel: 4 Reliable MethodsTo number lines in Excel, enter =ROW()-1 in the first numbering cell below a header in row 1,…
- Google Sheets COUNTIF Criteria: Syntax and ExamplesIn Google Sheets, a COUNTIF criterion is the condition that tells the function which cells to count. Use…
- Conditional Formatting to Change Cell Color in ExcelIn Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting and select…
- Free Excel Comparison Template: Build One That FitsYes. You can create a free Excel comparison template by listing your options in columns, recording the same…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.