Split Text to Columns in Excel: A Practical Guide

How-to basics · Updated November 21, 2026

To split text into separate Excel columns, select the source cells, then choose Data > Text to Columns. In the wizard, choose Delimited for separators such as commas or spaces, or Fixed width for breaks at specific character positions; set the output options and select Finish.

What Text to Columns Does

Text to Columns takes values stored in one column and separates each value into two or more columns. For example, it can turn a cell containing Jordan Lee, Portland, OR into separate fields for a name, city, and state. It changes the selected data in place unless you choose a different destination, so it is a useful cleanup tool for imported lists, copied records, and other text that follows a consistent pattern.

The tool is a one-time conversion, not a live link between the original text and the resulting columns. If you change a source value later, Excel does not automatically redo the split. For a split that recalculates when the source changes, use a formula such as TEXTSPLIT instead.

Prepare the Data Before Splitting

Before opening the wizard, check that the source values follow a pattern you can split reliably. A delimiter works well when the same character consistently marks a boundary. Fixed width works when the boundary is at the same position in every value. Inconsistent spelling, extra spaces, or missing fields can produce uneven results that need additional cleanup.

  • Make a backup: Copy the source column or duplicate the worksheet if the original values may be needed later.
  • Check the columns to the right: The split results expand into adjacent columns. Keep those cells empty or choose a separate destination so existing data is not displaced.
  • Select the intended rows: Include only the records to split. If a header is in the same column, decide whether it should be split too.
  • Look for exceptions: A value with an extra comma or a missing part may not fit the pattern used by the other rows.

For example, if a list contains full names in column A and other information already occupies columns B and C, do not start the split at A1 and assume the results will fit. Set a destination in unused columns, or move the existing information first.

Split by a Delimiter

Use Delimited when one or more known characters separate the parts. In Excel for Microsoft 365 on Windows, select the source range and open Data > Text to Columns. Choose Delimited, select Next, and mark the delimiter in the wizard. Options include Tab, semicolon, comma, space, and a character entered under Other. The preview shows where Excel expects to create columns.

You can select more than one delimiter. For instance, a list separated by commas and semicolons can be split on both. If the data has repeated separators, such as several spaces between words, select Treat consecutive delimiters as one when those repeated characters should count as a single boundary. This setting is not suitable when empty fields are meaningful: two adjacent commas in a data file may indicate a blank value that should remain blank.

The wizard also has a text qualifier setting. A qualifier tells Excel to treat characters inside a pair of quotation marks as part of one text value rather than as separators. For example, with a comma delimiter and double quotes as the qualifier, the comma inside "Portland, Oregon" can stay within that field. Check the preview carefully, especially when splitting imported data with quoted values.

When the preview looks right, select Next to review the column formats and destination, then select Finish. If the preview shows too many columns, too few columns, or a misplaced boundary, go back and adjust the delimiter or qualifier before applying the conversion.

Split at Fixed Character Positions

Choose Fixed width when each value has parts of consistent lengths and there is no reliable delimiter. In the wizard’s preview, click to add a break line, drag a line to reposition it, or double-click a line to remove it. For example, values such as 20261103AC17 could be divided after the first four characters, after the next two, and after the next two to separate a year, month, day, and code.

Fixed-width splitting is less appropriate for ordinary names or addresses because their lengths vary. A break set after the tenth character will split some names in the wrong place. Use this option for structured codes, exported reports, or records whose fields occupy stable character positions, and inspect the preview across multiple rows before finishing.

Choose the Output Format and Destination

In the final wizard step, select each previewed column to set its data format. General lets Excel interpret values as numbers, dates, or text. Choose Text when Excel must preserve the characters exactly, such as product IDs, postal codes, phone numbers, or codes with leading zeros. If a value such as 00427 is treated as a number, Excel can remove the zeros; formatting that output column as Text helps prevent that conversion.

For date fields, the wizard’s Date option lets you specify the order, such as MDY or DMY. This matters when values like 03/04/2026 could mean March 4 or April 3. Pick the order used by the source data rather than relying on how the date looks in the preview. When values are ambiguous or inconsistent, keep them as text and resolve the interpretation separately.

The Destination box controls where the first output column will go. If the source is in A2 and you choose B2 as the destination, the split fields begin in column B. This lets you preserve the original text while checking the results. Make sure the full output area is clear; otherwise, Excel may warn that data will be replaced or the results may conflict with existing content. You can undo a completed conversion with Ctrl+Z on Windows or Command+Z on Mac, provided you have not made intervening changes that make undo impractical.

Practical Examples

Suppose column A contains Chen, Maya, Seattle, with commas separating last name, first name, and city. Choose Delimited, select Comma, and review whether spaces remain at the start of the second and third fields. If they do, the results can be cleaned with Excel’s trimming tools or by choosing a delimiter pattern that matches the source. Do not select Space as an additional delimiter unless spaces inside names or locations should also create new columns.

A list of email addresses can be divided at the at sign when the goal is to separate the user name from the domain. Select Other and enter @; then check that each row has the expected two parts. This is a simple text split, not a check that the addresses are valid. Likewise, splitting a date-like code at hyphens separates its components but does not establish what those components mean.

For tab-separated text copied from another system, select Tab rather than typing spaces or guessing at column positions. Imported text may contain tabs even when they are not visible in the cell. The preview is the fastest way to confirm whether Excel recognizes the intended structure.

Use TEXTSPLIT for a Formula-Based Result

If the original text should remain untouched or the result needs to update when the source changes, use the TEXTSPLIT function. TEXTSPLIT is available in Microsoft 365 and Excel 2024. It returns a dynamic array that spills into neighboring cells, so those cells must be empty. It also cannot spill inside an Excel table.

With the text Maya,Seattle,WA in cell A2 and the output area starting in B2, enter =TEXTSPLIT(A2,","). The result is Maya in B2, Seattle in C2, and WA in D2. If A2 changes, the formula result updates. The formula splits on the exact comma; spaces after commas, if present in the source, remain in the output and may need trimming. Unlike Text to Columns, this approach preserves the source value and can be filled down for a list, as long as each formula has room to spill.

How to Choose the Right Method

Text to Columns is often the simplest choice for a one-time conversion with a consistent delimiter or fixed-width layout. TEXTSPLIT is a better fit when the split must update as source data changes or when you want to preserve the original column. For repeated imports that need the same cleanup each time, Power Query can record transformation steps and apply them again when refreshed.

In Excel for Microsoft 365 on Windows, a common route to import a text file into Power Query is Data > Get Data > From File > From Text/CSV. The available import options depend on the file and Excel build. This approach is useful when the split is part of a broader repeatable import process, but it is more setup than necessary for a small one-off list. Whichever method you choose, keep a copy of raw imported data when it may be useful for later checks.

Common Problems and Fixes

  • Everything stays in one column: The selected delimiter may not match the actual separator. Check the data preview and try Tab, another punctuation mark, or the Other box.
  • Names or phrases split too far: A space delimiter breaks at every space, including spaces inside a person’s name or a multiword city. Use a different delimiter or a formula designed for the exact pattern.
  • Leading zeros disappear: Select the affected output column and choose Text before finishing, rather than letting General convert the value to a number.
  • Dates change meaning: Select the correct date order in the column format step, or preserve ambiguous values as text until their source convention is known.
  • Results overwrite other cells: Choose a clear destination or move the data in the output columns first. Undo immediately if the result was not intended.
  • Some rows have a different number of fields: Inspect those source values for missing delimiters, extra delimiters, or inconsistent quoting. A split tool follows the structure it is given; it cannot infer a missing field reliably.

Excel for Mac and Excel for the Web

The desktop Excel wizard is also available in many current Mac versions under Data > Text to Columns, though ribbon layout and dialog appearance can differ from Windows. Follow the wizard’s preview and output-format options rather than relying on a Windows screenshot. The undo shortcut differs: use Command+Z on Mac and Ctrl+Z on Windows.

Excel for the web may present a different set of data tools depending on the current build and account. If Text to Columns is not available in the web ribbon, TEXTSPLIT is a useful alternative where the function is supported; otherwise, open the workbook in desktop Excel to use the wizard. Always check whether the results spill into clear cells before entering a dynamic-array formula.

Frequently Asked Questions

How do I split a full name into first and last name?
Select the name cells and use Data > Text to Columns with Space as the delimiter only if each name has exactly two parts. Names with middle names, compound surnames, or suffixes need a more deliberate approach; a space split can create more than two columns or divide a surname.

Can Excel split text by more than one delimiter?
Yes. In the Text to Columns wizard, select each delimiter that should mark a break, such as both commas and semicolons. Check the preview because every selected delimiter creates a boundary, including any that occur inside a value.

How do I keep the original text after splitting?
Set the wizard’s Destination to an empty column or range instead of the source location. You can also use TEXTSPLIT in a separate cell so the source remains intact and the formula output can update.

Does Text to Columns update automatically when the source changes?
No. It converts the selected values once. Use a formula such as TEXTSPLIT for a result that recalculates from the source, or use a refreshable Power Query process for recurring imports.

Why did Excel remove the zeros from my split result?
Excel likely interpreted a digit-only field as a number under the General format. Select that output column as Text in the wizard before finishing, or format and re-import the source carefully if the conversion has already happened.

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.