To convert text to columns in Excel for Microsoft 365 on Windows, select the cells in one column, then choose Data > Text to Columns. In the wizard, choose Delimited for values separated by characters such as commas or tabs, or Fixed width for values aligned at consistent positions; set the options and select Finish.
What Text to Columns does
Text to Columns takes text stored in a single column and divides it across two or more columns. It is useful when an imported list contains several fields in each cell, such as a full name and department separated by a comma, or a date and time separated by a space. The command changes the selected cells, so it is a data-cleanup tool rather than a way to create a live link to the original text.
For example, a cell containing Jordan Lee;Sales;Portland can be split into three cells: Jordan Lee, Sales, and Portland. The original text is replaced by the first result, while the additional results are placed in columns to its right. That behavior makes the command quick, but it also means you should check the destination area before finishing.
Prepare the worksheet first
Before splitting, make a copy of the source column or save a separate version of the workbook. Text to Columns can replace existing content in the columns where the results will go. If the source is in column A and the split produces three fields, the results need room in columns A through C. Any existing values in B or C could be overwritten.
- Check that the selected cells contain the rows you intend to split, and include a header only if you want it processed too.
- Make sure there is enough empty space to the right of the source column.
- Look for inconsistent separators, blank cells, or rows with a different number of fields.
- Keep an untouched copy when the data is important or difficult to recreate.
Text to Columns works on the selected range, and the wizard previews how the values will be divided. Inspect several representative rows in that preview, especially if the data came from a text file or another application. A comma-separated export may contain quoted commas that should remain part of a field, for instance.
Split values with delimiters
Choose the Delimited option when a character marks the boundary between fields. In the wizard, select the delimiter used by the data: Tab, semicolon, comma, space, or Other. For Other, enter the character in the box. The preview updates as you change the selection, so you can verify that each field lands in the intended column.
- Select the cells to split. Selecting a single cell within a contiguous list may cause Excel to detect the surrounding data, but selecting the exact range makes the intended scope clearer.
- Choose Data > Text to Columns.
- Select Delimited, then choose Next.
- Check the relevant delimiter and review the preview. Clear any delimiter that is not actually separating fields.
- Set the text qualifier if the source uses quotation marks around fields, choose Next, review the column formats and destination, then select Finish.
The text qualifier is important for data exported in a CSV-style format. A value such as "Lee, Jordan" may use a comma inside the quoted name; with the double-quote qualifier recognized, Excel can keep that comma inside one field rather than splitting the name. The wizard also offers an option to treat consecutive delimiters as one. Use it when repeated separators mean “one boundary,” such as several spaces between words. Leave it off when empty fields are meaningful: in a record such as Rita,,West, the two commas may indicate a blank middle field.
Be cautious with Space as a delimiter. It splits at every space, so a full name like “Jordan Lee” becomes two fields, and an address can break into many pieces. If the requirement is to separate a first and last name, that may be appropriate only when names consistently have exactly two parts. Names with middle names, compound surnames, or different spacing need a more deliberate approach.
Use Fixed width for aligned data
Choose Fixed width when the text is arranged in columns by character position rather than separated by a delimiter. This can happen in older reports with padded spaces, where every account number starts at the same position and every amount begins farther along the line. The wizard displays vertical break lines in its preview. Click to add a break, double-click a line to remove it, or drag a line to change its position.
Fixed width is appropriate only when the positions are consistent from row to row. If one row has a longer description and pushes later values sideways, a break at the same character position can divide that row incorrectly. Review both short and long examples before completing the operation. When alignment varies, a dependable delimiter or a purpose-built transformation is usually safer.
Choose column formats and destination
In the final wizard step, select a preview column and choose its data format. General lets Excel interpret values as numbers, dates, or text where it can. Text tells Excel to preserve the characters as text, which is often the right choice for identifiers such as postal codes, product codes, and account numbers. Date lets you specify the order used in a date string, such as MDY or DMY.
Format choice can prevent subtle changes. A code like 00427 may become the number 427 under General, losing its leading zeros. If those zeros are part of the identifier, set that output column to Text before selecting Finish. Likewise, an ambiguous value such as 03/04/2025 can mean March 4 or 3 April depending on date order and regional conventions. Choose the correct date format, or use Text if the value must remain exactly as written.
The Destination box controls where the first output column begins. By default, Excel places it at the source location, replacing the original text. You can specify another starting cell to preserve the source, but ensure the output area is empty. The destination must be a suitable worksheet range, and the split results still occupy adjacent columns from that starting point.
Practical examples and common problems
Suppose column A contains INV-2048|2026-06-15|Paid. With the vertical bar selected as the Other delimiter, Text to Columns can put the invoice number, date string, and status into three columns. If the date should be a real Excel date, inspect the format step and confirm that Excel recognizes the year-month-day order correctly. If it does not, retain the field as text or convert it separately using a method suited to the source format.
A frequent problem is unexpected splitting. This usually happens because an extra delimiter was selected, because a space was used on fields that contain spaces, or because the source has inconsistent punctuation. Another common issue is an empty result column overwriting useful data. If the outcome is wrong, use Undo immediately with Ctrl+Z on Windows; on Mac, use Command+Z. Undo is useful for a recent operation, but it is not a substitute for keeping a backup before cleaning important data.
- Leading zeros disappear: set the affected output column to Text before finishing.
- Dates change meaning: choose the intended date order or preserve the field as text.
- Blank fields vanish: review the consecutive-delimiter option and check whether empty values carry meaning.
- Rows split differently: inspect whether every row uses the same delimiter and pattern.
- Values overwrite other data: undo if possible, then repeat with a blank destination or a copied source column.
Text to Columns versus formulas and Power Query
Text to Columns is a good choice for a one-time, manual split. Its results are ordinary cell values, so they do not automatically update if the source text changes. A formula is more suitable when the split needs to recalculate as source cells are edited, while Power Query is useful for repeatable imports and more involved cleanup steps.
In Microsoft 365 or Excel 2024, the TEXTSPLIT function can split a value by a specified delimiter and spill the results into neighboring cells. With the text in A2 as Mira Patel,Seattle,WA, enter =TEXTSPLIT(A2,",") in an empty cell; the result is Mira Patel in the first output cell, Seattle in the next, and WA in the third. Leave the spill area clear. This formula is dynamic, unlike Text to Columns, and recalculates when A2 changes. TEXTSPLIT is not available in every older Excel release.
For repeated data imports, Power Query can preserve a sequence of transformation steps and apply them again when refreshing data. It is more capable than a one-off split, but requires learning its query workflow. If the task is simply to divide a clean list once, the Text to Columns wizard is often faster and easier to inspect.
Excel for Mac and Excel for the web
In Excel for Mac, the command is also available from the Data tab as Text to Columns, and the basic delimiter and fixed-width workflow is similar. Keyboard shortcuts differ across platforms: Ctrl+Z is Undo on Windows, while Command+Z is Undo on Mac. Ribbon layout and dialog details can vary by Excel release, so follow the labels shown in the installed version.
Excel for the web may have a different set of data tools and interface details from the desktop application. If Text to Columns is not available in the ribbon or the wizard does not provide the needed options, open the workbook in desktop Excel and perform the split there. In either version, make a copy first if preserving the original data matters.
Frequently Asked Questions
How do I separate first and last names in Excel?
Select the name cells and choose Data > Text to Columns, then select Delimited and choose Space. This works when each name has exactly one space between first and last names. For names with middle names or compound surnames, a space split can create extra columns and may need manual review.
Can Text to Columns split text by a character I choose?
Yes. In the Delimited step, select Other and type the character, such as a vertical bar or slash. Check the preview to make sure that character is a true separator and does not also occur inside values.
Will Text to Columns keep the original text?
Not if you accept the default destination, because the first output replaces the source cells. Set a different destination in the wizard or copy the source column elsewhere before splitting if you need to retain the original.
Why did Excel remove the zeros at the start of my values?
Excel interpreted the split field as a number under the General format. Choose the output column in the wizard and set its format to Text before finishing when leading zeros are significant.
Can I undo Text to Columns?
Usually, yes, immediately after the operation: press Ctrl+Z on Windows or Command+Z on Mac. If you have made further changes or closed the workbook, recovery may not be straightforward, which is why a saved copy is the safer option.
Related
- Goal Seek in Excel: Steps, Examples, and Calc Differences
- Building a Heat Map in Excel: Steps, Scales, and Tips
- Binary Excel Solver: How to Build and Solve 0-or-1 Models
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Conditional Formatting Rules: How to Use ThemIn Excel for Microsoft 365 on Windows, conditional formatting rules automatically change a cell’s appearance when its value…
- How to Create Mailing Labels in WordTo create mailing labels in Word, open the Mailings tab, choose Start Mail Merge > Labels, select the…
- How to Edit Date Format in Google Sheets: Complete GuideTo edit a date’s appearance in Google Sheets, select the date cells, then choose Format > Number and…
- Can You Consolidate Tabs in Excel? Methods ExplainedYes. In Excel for Microsoft 365, you can consolidate data from multiple worksheets with Data > Consolidate, or…
- How to Indent a New Line in Excel Using Cell AlignmentTo indent text in an Excel cell, select the cell and choose Home > Alignment > Increase Indent.…
- Excel Find and Replace in Selection: Steps and TipsTo use Find and Replace only in selected cells in Excel for Microsoft 365 on Windows, select a…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.