Excel Text to Columns: How to Split Data Into Fields

How-to basics · Updated November 21, 2026

In Excel for Microsoft 365 on Windows, select the cells you want to split, then choose Data > Text to Columns. In the wizard, choose Delimited to split at characters such as commas or spaces, or Fixed width to split at positions you set; check the preview and finish with a destination that will not overwrite data.

What Text to Columns does

Text to Columns takes text stored in one column and distributes its parts across two or more columns. It is useful when a cell contains several pieces of information separated by a consistent character, or when records follow a fixed layout. For example, it can turn a column of comma-separated names and departments into separate fields, or divide a fixed-width code at chosen character positions.

The command changes the selected cells in the worksheet. It does not create a live connection to the original text or automatically repeat the split when the source changes. If you need a repeatable import or transformation, Power Query may be a better fit.

A common example is a column containing Lee,Jordan,Sales. With the comma selected as the delimiter, the result is three adjacent cells: Lee, Jordan, and Sales. Text to Columns does not know what those pieces mean; it follows the split settings you choose.

Delimited or fixed width: which option to choose?

Choose Delimited when a recognizable character marks the boundary between fields. Common delimiters include commas, tabs, semicolons, spaces, and a character you enter in the Other box. A tab-separated list, for instance, can be split by selecting Tab; an address line such as Portland,OR,97205 can be divided at commas.

Choose Fixed width when each field occupies a predictable number of character positions, even though no separator appears between fields. A file might use positions 1–8 for an account code, 9–18 for a date, and the remaining characters for a value. In the wizard’s preview, add or move break lines to mark where one field ends and the next begins.

  • Use Delimited for data such as red|green|blue, where the vertical bar is the separator.
  • Use Fixed width for consistently aligned text records with no clear delimiter.
  • If records have inconsistent spacing or separators, inspect the data first; one set of split rules may not produce reliable fields for every row.

How to use the Text to Columns wizard

  1. Prepare the worksheet. Make a copy of important source data or confirm that you can undo the operation. Select the cells in one column that contain the text to split. Leave enough empty columns to the right for the expected results.
  2. Open the command. On Windows, go to Data > Text to Columns. The wizard opens with the choice between Delimited and Fixed width.
  3. Choose the split method. Select Delimited for separator-based text, or Fixed width for position-based text, then continue to the next wizard step.
  4. Set the delimiter or breaks. For Delimited, check the relevant separator boxes or enter a character under Other. For Fixed width, use the preview to add, move, or remove break lines.
  5. Check the preview. Confirm that each sample value lands in the intended field. Adjust settings if a name, date, or other value is being split in the wrong place.
  6. Set output options and finish. In the final step, choose column formats if necessary and set a Destination if the output should go somewhere other than the selected range. Click Finish.

Excel for Mac also provides Text to Columns on the Data tab, although the wizard’s appearance and some controls can vary by release. The basic choices and workflow are similar. If a command is not visible, check the Data tab or the application’s ribbon customization; keyboard shortcuts and interface details can differ between Windows and Mac.

Delimiters, spaces, and text qualifiers

The delimiter settings determine exactly where Excel makes a split. Selecting more than one delimiter means any selected separator can create a boundary. That can help when a file mixes commas and tabs, but it can also create extra columns if the data contains a character you did not intend to use.

Spaces deserve special attention. Selecting Space splits at every space, so a full name such as Jordan Lee becomes two fields, while a multiword street name may become several. If repeated spaces should count as a single separator, use the wizard’s Treat consecutive delimiters as one option. This is not appropriate when the number of spaces carries meaning or when empty fields must be retained.

A text qualifier tells Excel that text enclosed by a chosen character should be treated as one field, even if it contains a delimiter. The common double-quote qualifier is useful for comma-separated text such as "Lee, Jordan",Sales: the comma inside the quoted name should not split that name into two columns. Check the preview, especially when the source has unmatched or unusual quotation marks.

Choose formats to protect important values

Excel interprets many results as numbers, dates, or general values unless you tell it otherwise. That can be convenient for ordinary numeric data but damaging when the characters are identifiers rather than quantities. A postal code such as 02108 may lose its leading zero if Excel converts it to a number. Select the relevant output column in the wizard and set its format to Text to preserve the characters.

Dates can also be misread when their order is ambiguous. In the final wizard step, select the date format that matches the source—for example, DMY for day-month-year text—rather than relying on Excel to infer the intended order. A value such as 03/04/2026 could mean March 4 or April 3 depending on the date convention. Review the preview and the finished cells before using the results.

The final step also allows a column to be skipped with Do not import column (skip) when that option is available in the wizard. This can be useful if an input file includes a field you do not need. Formatting choices apply to the output columns created by the split, not as a general cleanup of unrelated worksheet data.

Destination, overwriting, and preserving the original

Text to Columns writes its results into worksheet cells. If the output starts in the source column, the first result replaces the original text and additional results fill cells to its right. Data already in those destination cells can be overwritten, so leave a clear area or choose a different Destination in the final wizard step. When in doubt, copy the source column to a spare worksheet or workbook first.

For example, if a selected cell in A2 will split into three fields, Excel needs room for the output in A2:C2, and the same applies to every selected row. A value in B2 or C2 may be replaced by the result. Choosing a destination such as E2 keeps the original column in place, provided the destination area is empty.

After splitting, the new columns are separate values; they are not linked back to the original text. You can use Undo immediately if the result is wrong, but it is safer to prepare a copy before a large transformation. Saving a workbook before a significant change also gives you a recovery point.

Common problems and how to fix them

  • Everything stays in one column: The chosen delimiter may not match the source. Check the preview and try Tab, comma, semicolon, Space, or the correct character under Other.
  • Words split into too many pieces: A selected space or punctuation mark may occur inside values. Remove that delimiter, or use a text qualifier where the source encloses complete fields in quotation marks.
  • Blank fields disappear: Consecutive delimiters can be treated as one when that option is selected. Turn it off if adjacent separators represent an empty field that must remain.
  • Leading zeros vanish or dates change: Excel has interpreted the output as a number or date. Set the affected column to Text, or choose the correct date order in the wizard before finishing.
  • Results overwrite nearby content: Cancel if you have not finished, then clear a sufficiently wide output area or select a different destination.
  • Some rows split differently: The source may not use a consistent pattern. A single delimiter or fixed-width layout cannot reliably resolve every irregular record; clean the input or use a more flexible transformation process.

Text to Columns compared with other Excel options

Text to Columns is a quick, one-time worksheet operation. It suits a small or moderate block of text when the split rule is clear. It is less suitable when new files arrive regularly, the source changes, or the cleanup involves several steps that need to be repeated consistently.

Power Query, available through Data > Get Data and related commands in Excel for Microsoft 365, can import and transform data through a sequence of recorded steps. It is useful for repeatable work, such as importing a recurring CSV file and splitting a column each time it is refreshed. The exact import commands depend on the source and Excel build.

For a formula-driven result that updates as source text changes, Excel 2024 and Microsoft 365 include functions such as TEXTSPLIT. For example, with a comma-separated value in A2, =TEXTSPLIT(A2,",") returns the separated items across columns. Unlike Text to Columns, a formula result recalculates when A2 changes; function availability and behavior can depend on the Excel version. For a one-time split without formulas, the ribbon command remains straightforward.

Frequently Asked Questions

Where is Text to Columns in Excel?
In Excel for Microsoft 365 on Windows, select the source cells and choose Data > Text to Columns. Excel for Mac also places the command on the Data tab, though the interface can vary slightly by version.

Can Text to Columns split a full name into first and last names?
Yes, if the names use a consistent separator. Choosing Space will split at every space, however, so a middle name or multiword surname can create extra fields. Review the preview and decide how those cases should be handled.

Does Text to Columns delete the original text?
It writes split results into worksheet cells and can replace the source when the output begins in the same location. Choose a separate destination or copy the original column first if you need to preserve it.

Can I split text at a character that is not listed?
Yes. Choose Delimited, select Other, and enter the separator character. Check the preview to make sure that character appears only where you intend to split.

Will the split update when the source text changes?
No. Text to Columns produces worksheet values rather than a live formula connection. Use a formula such as TEXTSPLIT in supported versions, or consider Power Query for a repeatable import and transformation.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.