Data to Columns in Excel: Split Text Step by Step

How-to basics · Updated November 18, 2026

To split a column in Excel, select the cells, then choose Data > Text to Columns. Choose Delimited to split at characters such as commas or tabs, or Fixed width to split at set positions; check the preview and destination before selecting Finish.

What Text to Columns Does

Text to Columns takes text from one column and distributes its parts across neighboring columns. For example, a cell containing Chen, Mira can be split at the comma so that Chen and Mira appear in separate cells. The command changes worksheet values; it does not merely change how a value looks.

The tool is useful for cleaning up pasted lists, separating names, extracting fields from a simple text export, or dividing codes that follow a consistent pattern. It works on selected cells in a worksheet and offers a preview before applying the split. It is not a general-purpose database parser: irregular records, nested formats, or text with inconsistent separators may need additional cleanup.

These steps describe Excel for Microsoft 365 on Windows. The Text to Columns command is also available in many desktop editions on Mac, although ribbon layout and dialog appearance can differ. In Excel for the web, availability and interface can vary; if the command is not shown, open the workbook in the desktop app.

Prepare the Data Before Splitting

Text to Columns can overwrite cells to the right of the selected source column. Before starting, check that those cells are empty or choose a different destination in the wizard. If the existing data matters, make a copy of the worksheet or duplicate the source column first. An accidental overwrite can be difficult to reconstruct, particularly after saving or making other edits.

Also inspect whether every row follows the same pattern. A list such as Rao, Nisha is straightforward, but a column that mixes Rao, Nisha with Nisha Rao does not have one consistent comma-delimited structure. Decide what each output column should contain before choosing a separator.

  • Include the full range of records, but avoid selecting unrelated columns.
  • Check for blank rows, extra spaces, and inconsistent punctuation that could create unexpected output.
  • Note identifiers with leading zeros, such as 004812, because Excel may convert them to a number unless the output is treated as text.
  • Keep a copy of the original text if you may need to verify or redo the transformation.

How to Use Text to Columns

  1. Select the cells containing the text. You can select a single column or just the rows you want to split. Including the column heading is optional; if included, Excel will process it like any other value.
  2. On the ribbon, choose Data > Text to Columns. This opens the Convert Text to Columns Wizard.
  3. In Step 1, choose Delimited if a character separates fields, or Fixed width if field boundaries occur at consistent positions. Select Next.
  4. For delimited text, select the separator in Step 2, such as Comma, Tab, Semicolon, or Space. To use another character, select Other and enter it. Review the data preview and adjust the text qualifier if needed.
  5. In Step 3, select a column in the preview if you need to set its data format. Choose General, Text, or Date, or choose Do not import column (skip) to omit a field. Set the destination if you want the result somewhere other than the source column.
  6. Select Finish. Review the output immediately, especially dates, codes, and any cells that were next to the source data.

The wizard’s preview is important: it shows how Excel interprets the chosen rules before committing them. If the preview shows an entire row in one field, the selected delimiter may not match the actual text. If it shows too many fields, the delimiter may also occur inside values.

Delimited or Fixed Width?

Choose Delimited when a character marks the boundary between fields. A comma-separated list such as North,17,Open can be split into three columns by selecting Comma. A tab-separated list pasted from another system can be split with Tab. For a text file that uses a pipe, as in North|17|Open, choose Other and enter the vertical bar.

Choose Fixed width when fields occupy consistent character positions rather than being separated by a dependable character. The preview displays suggested break lines; click to add a line, drag a line to move it, or remove an unwanted line. This can work for a report where each record uses aligned character positions. It is less suitable when names or descriptions have varying lengths and the column positions shift from row to row.

One setting to check for delimited data is Treat consecutive delimiters as one. For example, if multiple spaces separate fields, turning this on can prevent each extra space from creating an empty column. Leave it off when repeated separators intentionally represent blank fields, such as two commas in a row meaning that a value is missing.

Choose the Right Output Format

Excel’s default General format attempts to interpret the result. That is convenient for ordinary numbers, but it can change data that only resembles a number. An account code of 004812 may become 4812; a long identifier may display in scientific notation or lose precision; and a date-like string may be converted into a date value. For codes, postal codes, product numbers, or other values whose exact characters matter, select the relevant output column in the preview and choose Text.

If one output field contains dates, select that column and choose Date, then pick the order shown by the source, such as MDY or DMY. This matters when a value such as 03/04/2025 could mean March 4 or 3 April. The wizard’s date choice tells Excel how to read the source; it does not make an ambiguous source unambiguous. Confirm the original system’s date convention before converting.

You can set different formats for different output columns. For example, a split record might contain a text product code, a date, and a quantity. Set the code column to Text, the date column to the correct Date order, and leave the quantity as General if it should be numeric. This avoids converting every field according to one rule.

Practical Examples and Common Problems

Suppose A2 contains Mira Chen, with one space between first and last name. Selecting the source cells and using Space as the delimiter produces Mira and Chen in adjacent columns. This simple split assumes that each record has exactly two parts. A name such as Mary Ann Chen creates three parts, so decide whether the desired output is first name, middle name, and last name or whether a different cleanup method is needed.

For an address such as Chen, Mira, Seattle, choosing Comma produces three fields only if the commas truly mark the intended boundaries. If an address field itself contains a comma and is enclosed in quotation marks, the text qualifier can tell Excel to keep that quoted portion together. Check the preview rather than assuming that every comma should split a value.

Common surprises include:

  • Existing cells disappear: output landed in occupied columns. Undo immediately if possible, or restore the backup, then rerun with a safe destination.
  • Leading zeros vanish: Excel treated a code as a number. Undo and rerun with the output column set to Text; changing a shortened number back to text cannot recover zeros that were already discarded.
  • Dates look reversed: Excel interpreted the source using a different date order. Undo and select the correct Date format in the wizard.
  • Extra blank columns appear: repeated delimiters were treated separately, or the selected delimiter also occurs in the content. Review the consecutive-delimiter option and the preview.
  • Only one part appears: the delimiter may be a different character, such as a tab rather than a space. Inspect the source text and test the correct option.

Text to Columns Versus Other Methods

Text to Columns is a practical choice for a one-time split or a small, consistent list. It applies a conversion to the selected range and is easy to inspect in the wizard. If new rows will arrive regularly and the transformation must be repeated, consider a formula or Power Query so the process can be updated more deliberately.

In Microsoft 365 or Excel 2024, the TEXTSPLIT function can split a value with a formula and leave the original text unchanged. With the text in A2 containing Mira,Chen,Seattle, enter =TEXTSPLIT(A2,",") in B2; the example returns Mira, Chen, and Seattle in neighboring cells. The formula’s results spill into adjacent cells, so those cells must be empty. Unlike the wizard, the formula can update when A2 changes, but it requires a version that supports TEXTSPLIT and a consistent delimiter.

Power Query is often a better fit for repeated imports or multi-step cleanup. It can record transformation steps and refresh them when a source is updated. For a single quick split, setting up a query may be unnecessary. If data comes from a CSV file, importing it through Excel’s data import tools can also give more control over column types than opening a file and accepting automatic conversions.

Frequently Asked Questions

Does Text to Columns delete the original data?
It writes the split results beginning at the source or at the destination you specify. The source column may be replaced by the first output field, and cells to its right can be overwritten. Choose a separate destination or make a backup when the original must be preserved.

How do I split text by a comma in Excel?
Select the text cells, choose Data > Text to Columns, select Delimited, choose Comma, inspect the preview, set formats if needed, and select Finish. If commas appear inside quoted values, check the text qualifier and preview before applying the split.

How can I split a column but keep leading zeros?
In the wizard’s final step, select the output column in the preview and choose Text before finishing. This tells Excel to preserve values such as 004812 as text rather than converting them to the number 4812.

Can Text to Columns split text at a space?
Yes. Choose Space as the delimiter. If multiple spaces should count as one boundary, select Treat consecutive delimiters as one. Be aware that this may not suit full names or addresses where spaces are part of a field.

Can I undo Text to Columns?
Immediately after applying it, use Undo on the Quick Access Toolbar or press Ctrl+Z on Windows. On Mac, use Command+Z. Undo may not be available after closing the workbook or after a sequence of later actions, so keeping a copy is safer for important data.

Is Text to Columns available in Excel for Mac?
Desktop Excel for Mac includes a Text to Columns command, though its ribbon and wizard can look different from Windows. Look under the Data tab for the command. Keyboard shortcuts and exact navigation can differ, so use the ribbon if a Windows shortcut does not apply.

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.