Microsoft Excel Text to Columns: Split Data Clearly

To convert text to columns in Microsoft Excel for Microsoft 365 on Windows, select the cells, go to Data > Text to Columns, choose Delimited or Fixed width, set the split points or separators, and select Finish. The wizard separates the contents into neighboring columns; check the preview and choose a safe destination first so you do not overwrite existing data.

What Text to Columns does

Text to Columns takes values stored together in one column and divides them across two or more columns. It is useful when an export, pasted list, or downloaded file puts several fields into each cell. For example, a column containing Riley Chen, Vancouver, BC can be split at commas into separate fields. A value such as 2026-11-02 can also be split at hyphens if you need its year, month, and day as separate pieces.

The command changes the selected cells rather than creating a live relationship to the original text. After splitting, the resulting pieces are ordinary cell values. If the source changes later, Excel will not automatically redo the split. That distinction matters when deciding between this wizard and a formula.

Text to Columns is a transformation tool, not a full data-cleaning system. It can divide a string based on separators or character positions, but it does not reliably infer every intended field or repair inconsistent source data. Previewing the result and checking a few rows remains important, especially when the list contains names, addresses, dates, or identifiers.

Prepare the data and choose a destination

Start with a copy of the workbook or duplicate the source column if the original data is important. Select the cells you want to split, including the relevant rows. If you select a whole column, Excel can process blank cells too; selecting just the populated range makes the scope easier to understand. Avoid selecting unrelated columns because the wizard applies to the selected data.

Excel normally places the first output field in the leftmost selected column and writes later fields into columns to its right. If those cells already contain information, the split can replace it. Before you finish, inspect the preview and make sure the output area is empty, or use the wizard’s Destination box to specify another starting cell, such as D2. The destination is the top-left cell for the results, not a separate worksheet selector.

Keep a backup or copy of the source column if you may need to revisit the original text.
Check that enough blank columns are available for the widest row.
For a table, consider whether the added output columns should become part of the table before running the command.
Use a destination outside the source range when you want to preserve the original values.

Use the Delimited option

Choose Delimited when a character, such as a comma or tab, separates the fields. In the wizard, select the delimiter that appears in the source, then review the data preview. Excel offers common delimiter choices including Tab, Semicolon, Comma, and Space, as well as Other, where you can enter a character. The displayed preview updates as you change the selection.

Select the source cells and open Data > Text to Columns.
Choose Delimited, then select Next.
Check the appropriate delimiter boxes. If the separator is not listed, select Other and enter it.
Review the preview, adjust text-qualifier or consecutive-delimiter settings if needed, and select Next.
Choose column formats and a destination if appropriate, then select Finish.

For example, if A2 contains Chen;Riley;Vancouver, choosing the semicolon delimiter produces three fields in adjacent cells. A comma-separated row such as Chen,Riley,Vancouver calls for the comma option instead. Do not choose Space just because the data looks like words: a full name such as Riley Van Chen would split into three pieces, which may not be what you intend.

The Treat consecutive delimiters as one setting is useful when repeated separators should not create empty fields. For instance, it can help with data separated by varying numbers of spaces. Leave it unchecked when empty fields carry meaning. In a comma-separated record such as Chen,,Vancouver, the empty middle field may represent a missing value; treating consecutive delimiters as one can change how that gap is represented.

Handle quoted text and multiple separators

Delimited data may contain a separator as part of a field. A common example is a quoted name or address: "Chen, Riley",Vancouver. In this case, the comma inside the quotation marks is not intended to separate columns. The wizard’s Text qualifier setting, commonly a double quote, tells Excel to treat separators inside qualified text as part of the field. Check the preview to confirm that quoted commas stay within the intended value.

If the data uses more than one kind of separator, a single run may not produce the layout you want. You can sometimes split once on the main delimiter and then split one of the resulting columns again. For example, split an address export at tabs first, then split a combined city-and-region field at a comma. This staged approach gives you more control than selecting separators that would divide every occurrence throughout the entire row.

Irregular exports can also contain unmatched quotation marks, separators used inconsistently, or separators embedded in unquoted text. The wizard cannot know which interpretation the sender intended. If the preview shows a row splitting differently from its neighbors, inspect the raw source and correct or isolate those records before relying on the result.

Use Fixed width for position-based fields

Choose Fixed width when fields line up at consistent character positions and there is no dependable delimiter. This is common in older reports or text files formatted in columns. The wizard displays a preview with vertical break lines. Add or move a break where one field ends and the next begins, and check the preview against several rows before continuing. A line break that fits one record may cut through a value in another if the source is not truly fixed width.

For example, suppose each line contains a six-character code followed by a name in a consistently aligned position. Fixed width can separate the code from the remainder even if the name itself contains spaces. By contrast, using Space as a delimiter could divide the name into separate words. Fixed width is less suitable for lists where field lengths vary, because character positions will not consistently identify the boundaries.

After setting the breaks, select Next and review the output columns and formats just as you would with Delimited. If the preview reveals shifted or truncated values, go back and adjust the break positions rather than accepting an incorrect split.

Preserve dates, leading zeros, and other formats

On the wizard’s final step, you can set a format for each output column. General lets Excel interpret values, which is convenient for ordinary numbers but can be risky for codes and dates. A value such as 00127 may become 127 if Excel treats it as a number. Select that output column in the preview and choose Text when every character, including leading zeros, must be retained.

Date-looking strings need particular care. Excel may interpret a date according to regional conventions, so 03/04/2026 could be read as March 4 or April 3 depending on the expected month-day order. If the values are dates, choose the appropriate date order in the column data format rather than assuming Excel will infer the intended one. If the string is an identifier that only resembles a date, use Text instead.

To skip an unwanted field, select its column in the preview and choose Do not import column (skip), where that option is available in the wizard. The Advanced settings can also specify decimal and thousands separators for numeric data. These settings matter when a file uses conventions different from your Excel regional settings—for example, a comma as the decimal mark and a period as the thousands separator. Verify the preview and resulting values before using them in calculations.

Check the result and fix common problems

After selecting Finish, scan the output from top to bottom. Compare several rows to the source, including the longest entries and any rows with missing fields. If you notice a mistake immediately, use Ctrl+Z on Windows to undo it; on Mac, use Command+Z. Undo may no longer be practical after later changes, which is another reason to keep the original column or a backup.

Everything lands in one column: Check that you selected the correct delimiter or added the actual character under Other. The visible separator may be a tab rather than several spaces.
A field breaks into too many pieces: Try a more specific delimiter, use the text qualifier for quoted fields, or split in stages. Avoid Space when spaces occur inside values.
Values disappear or are overwritten: Undo immediately if possible, then repeat the operation with a clear destination and sufficient blank columns.
Leading zeros vanish or dates change: Set the affected output column to Text or select the correct date order before finishing.
Rows produce different numbers of fields: The source may contain inconsistent separators, blank fields, or embedded delimiters. Inspect those rows instead of assuming one setting will repair every record.

If the command is unavailable in the version you are using, open the workbook in the Excel desktop app and look under Data. The Windows steps above describe Microsoft 365 for Windows. Excel for Mac has a Text to Columns command on the Data tab, though ribbon layout and dialog presentation can differ. In Excel for the web, available commands can vary; if you cannot find the wizard, use the desktop app for this workflow. The Windows keyboard shortcut Alt+A, E opens Text to Columns in many current desktop ribbon configurations; keyboard access can differ on Mac and in the web app, so the ribbon path is the dependable route.

Text to Columns compared with formulas and imports

Use the wizard for a one-time split when you want static results and the source is reasonably consistent. If the original text will change and you want the separated values to update, a formula can be a better choice. With the comma-delimited text in A2, Microsoft 365 or Excel 2024 can use =TEXTSPLIT(A2,",") to return the pieces across cells. The formula spills into neighboring cells, so those cells must be empty. TEXTSPLIT is not available in every older Excel version.

For recurring imports, consider using Power Query rather than repeating a manual split. Power Query can import a file, split a column by a delimiter, and apply the steps again when refreshed. It is more setup than Text to Columns, but can be useful when the same cleanup is needed for new files on a regular basis. For a one-off adjustment, the wizard is usually simpler.

Text to Columns is also different from opening a CSV file. A CSV import may parse separators while bringing the file into Excel, and the import experience gives you a chance to control data types before values are loaded. Opening a CSV by double-clicking can apply automatic interpretations, which may alter codes or dates. When preserving exact source values matters, use an import workflow that lets you inspect formats, or set the relevant columns to Text during the split.

Frequently Asked Questions

How do I split first and last names in Excel?
Select the name cells, open Data > Text to Columns, choose Delimited, and select Space. This works when each name has exactly the structure you expect. Names can contain middle names, multiple spaces, or compound surnames, so inspect the results rather than assuming every person’s name divides into only two fields.

Can Text to Columns split text into rows instead?
No. The wizard splits values horizontally into adjacent columns. To place separate pieces down rows, use a suitable formula or another transformation workflow, such as Power Query, and check that the chosen method matches your Excel version.

Does Text to Columns change the original data?
It writes the split results into cells, usually starting in the selected column, so it can replace existing contents in the output area. Use a backup or set a different destination if you need to keep the original text unchanged.

Why does Excel remove zeros from my split values?
Excel may interpret a digit-only result as a number, and numbers do not retain leading zeros as displayed characters. In the wizard’s final step, select the relevant output column and choose Text before finishing.

Can I split a column using a character that is not listed?
Yes. In the Delimited wizard, select Other and enter the separator character, then review the preview. If the character also appears inside fields, check whether a text qualifier or a staged split is needed.

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.