To separate text into columns in Excel for Microsoft 365 on Windows, select the cells, choose Data > Text to Columns, select Delimited or Fixed width, set the options, and choose Finish. The tool splits the selected column into multiple columns, so make sure the cells to the right are empty or choose a different destination before you run it.
What Text to Columns does
Text to Columns takes data stored in one column and divides it across adjacent columns. You choose how Excel identifies each boundary: a character such as a comma or tab, or a position in the text. For example, a cell containing Jordan Lee,Seattle,WA can become three cells: Jordan Lee, Seattle, and WA.
This is a data-cleanup tool, not a way to split a single cell visually across lines. Once separated, each piece is an independent cell that you can sort, filter, format, or use in formulas. The original text is replaced at the starting cell by the first result, and additional results fill the cells to its right. If those cells already contain information, the output can overwrite it.
The command is useful for one-time or occasional cleanup, such as splitting imported contact details or separating codes. For a recurring import, Power Query or a formula may be easier to refresh or maintain.
Delimited versus fixed-width data
The wizard starts by asking whether the text is Delimited or Fixed width. Choose based on what actually marks the boundaries in your data.
- Delimited: Each field is separated by a character or symbol. Common examples are commas in a CSV-style list, tabs between copied fields, semicolons in some regional exports, and spaces between words.
- Fixed width: Each field occupies a predictable position, even when there is no separator. For instance, a report might put a four-character department code in positions 1–4 and an eight-character date in positions 5–12.
Delimited is usually the right choice for clearly separated values. Fixed width suits structured reports where columns line up at consistent character positions. It is a poor fit when field lengths vary: a name may be short or long, so placing a break after a fixed number of characters can split it incorrectly.
How to use the Text to Columns wizard
- Prepare the worksheet. Keep a copy of the original data or duplicate the worksheet. Check that enough empty columns are available to the right of the selected cells, or plan to use a blank destination.
- Select the source cells. Select the cells in one column that contain the text to split. Include the relevant rows, but avoid selecting unrelated data that should not be changed.
- Open the wizard. Choose Data > Text to Columns. In the first step, select Delimited or Fixed width, then select Next.
- Set the split points. For Delimited, check the separator used by the data, such as Comma, Tab, Semicolon, or Space. Choose Other to enter a different character. For Fixed width, add or move the break lines in the preview to mark where each output column should begin.
- Check the preview and options. Confirm that the sample is divided as intended. In the final step, set any column formats and the destination if needed.
- Finish and inspect the results. Select Finish, then check several rows, especially entries with unusual punctuation, missing fields, or longer-than-usual values.
The preview is important: a setting that works for most rows can still split an exception incorrectly. If the preview shows the wrong boundaries, go back and adjust the delimiter or break lines rather than accepting a flawed result and repairing every row afterward.
Choosing delimiters and handling quotes
On the Delimited step, select only the separator or separators that represent real field boundaries. Checking both comma and space, for example, splits at every comma and every space. That can be helpful for a carefully structured list but will split a full name into separate first- and last-name columns as well.
The option Treat consecutive delimiters as one is useful when repeated separators are merely padding, such as several spaces used to line up report fields. Leave it unchecked if repeated delimiters represent empty fields. In a comma-separated record such as Riley,,Portland, the empty position between commas may stand for a missing value; treating consecutive delimiters as one can remove that empty column and shift the meaning of later fields.
The Text qualifier option, commonly set to a double quote, helps Excel recognize text surrounded by quotes. In a properly quoted comma-separated record, a comma inside a quoted field is part of that field rather than a boundary. Check the preview with representative rows, because inconsistent or malformed quoting can still produce unexpected splits. If the source file has complex quoting rules, import it through Excel’s data-import tools or Power Query and review the parsing options rather than assuming a simple split will handle every case.
Preserve dates, IDs, and leading zeros
Text to Columns can change how values are stored. In the final wizard step, Excel’s General column format interprets values where it can. That may turn digit strings into numbers, interpret date-like text as dates, or remove leading zeros. A code such as 00427 may become the number 427, which is not equivalent if the zeros are part of the identifier.
To keep a split field exactly as text, select its column in the preview and choose Text as the column data format. This is often appropriate for postal codes, account references, product codes, and phone numbers. For dates, use the Date option and select the order that matches the source, such as DMY or MDY. An ambiguous value like 03/04/2026 can mean different dates in different date-order conventions, so verify it rather than relying on appearance alone.
Text formatting preserves characters but does not make text behave like a numeric value in calculations. If a field is genuinely a quantity or date that you need to calculate with, choose an appropriate date or numeric interpretation and verify the result. Formatting a cell to display leading zeros is another option for numeric identifiers, but it changes the display rather than preserving the original string as text.
Choose an output destination and protect your data
By default, the first split field starts at the selected range’s starting cell, with later fields placed in columns to its right. The final wizard step includes a Destination box. Point it to an empty cell if you want to keep the original column in place; check that the entire output area is clear before finishing. A destination does not automatically create a new worksheet, so select a suitable blank area or make a copy of the sheet first.
Text to Columns acts on the selected data and can replace its contents. A safe workflow is to duplicate the source column or worksheet, split the copy, and compare the output with the original. This is especially sensible for data received from another person, a downloaded report, or a system export that would be difficult to retrieve again.
- Check the number of output columns needed before starting.
- Keep an untouched copy until you have verified the split.
- Review rows with blank fields, extra delimiters, or unusually long text.
- Afterward, check that dates, IDs, and numbers have the intended data types.
Common problems and practical fixes
Everything lands in one column. The chosen delimiter may not match the source. Reopen the wizard on a copy and check whether the data uses tabs, semicolons, a different character, or fixed-width positions. Data copied from a web page can contain tabs or nonstandard spaces rather than ordinary spaces.
Names or addresses split in the wrong place. A space is not always a safe delimiter. Names can contain middle names, and addresses can contain multiple words. Use a more reliable separator if one exists, such as a comma between fields, or use a method that can account for the actual data structure.
Blank fields disappear or later values shift. Check whether consecutive delimiters represent missing fields. Turn off Treat consecutive delimiters as one when each delimiter marks a separate field, including an empty one.
Leading zeros vanish or dates change. Set the affected output column to Text or choose the correct date order in the final step. If the split has already happened, restore the original data and repeat with the right format; changing a cell to Text afterward cannot reliably restore zeros that Excel has already discarded.
Results overwrite neighboring data. Undo immediately with Ctrl+Z if possible, then repeat with a blank destination or after moving the neighboring content. On Mac, use Command+Z to undo. If you have made further changes since the split, undo may also reverse those later actions, so checking the destination beforehand is safer.
Text to Columns, formulas, and Power Query
Text to Columns is convenient for a one-time split, but it is not a live link to the source. If the original cell changes later, the separated cells do not update automatically. A formula can be a better choice when you want results to recalculate as the source changes. In Microsoft 365 or Excel 2024, TEXTSPLIT can divide text using a delimiter.
With the full comma-separated string in A2, enter this formula in B2 to split at commas:
=TEXTSPLIT(A2,",")
For the value Jordan Lee,Seattle,WA in A2, the formula returns Jordan Lee in B2, Seattle in C2, and WA in D2. The result spills into adjacent cells, so those cells must be clear. This simple example treats commas as separators; it is not a substitute for parsing every possible quoted CSV file. For repeated imports or larger datasets, Power Query can load and transform data through a repeatable set of steps. It is more setup than a one-off wizard, but refreshing the query can apply the same transformation to updated source data.
Excel for Mac also provides Text to Columns through the Data tab, though ribbon layout and dialog appearance can differ by version. On Excel for the web, command availability and layout can vary; if Text to Columns is not available in the ribbon, open the workbook in the desktop app. The steps and examples here describe Microsoft 365 for Windows.
Frequently Asked Questions
Can I split text into columns without changing the original?
Yes. In the wizard’s final step, set Destination to a blank area, or duplicate the source column or worksheet and split the copy. Confirm that the output area has enough empty columns.
How do I split a full name into first and last names?
If every name has exactly two parts separated by one space, choose Space as the delimiter. If names can contain middle names, multiple spaces, or compound surnames, a simple space split may not produce reliable first- and last-name fields.
Can Text to Columns split by a character other than a comma?
Yes. In the Delimited step, choose Other and enter the separator character. You can also select built-in options such as Tab, Semicolon, or Space.
Why did Excel remove zeros from my codes?
Excel likely interpreted the split values as numbers under the General format. Repeat the split and set the relevant output column to Text in the final step to preserve leading zeros.
Can I undo Text to Columns?
Use Ctrl+Z on Windows or Command+Z on Mac immediately after the operation. If you have continued editing, the safer option may be to restore the untouched copy of the source data.
Related
- Load Query Data into an Excel Table: A Practical Guide
- Load Excel Data Into a SQL Table: A Practical Guide
- Load Excel Power Query Into an Existing Table
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Lock a Named Range in Excel Without Locking Other CellsTo lock a named range in Excel, select the cells it refers to, make sure their Locked setting…
- Google Sheets Date Format Australia: Set DD/MM/YYYYTo use Australian dates in Google Sheets, set the spreadsheet locale to Australia under File > Settings, then…
- Insert Image in Excel Cell With Text: A Practical GuideIn Excel for Microsoft 365 on Windows, select a cell and choose Insert > Pictures > Place in…
- Outlook Signature: Create a Professional Email Sign-OffTo create a professional signature in Outlook, open the signature settings for your version of Outlook, create a…
- How to Freeze the First Row in Excel on Any DeviceTo keep the first row visible while you scroll in Excel for Microsoft 365 on Windows, select View…
- How to Make a Copy of a Sheet in ExcelTo copy a sheet in Excel for Microsoft 365 on Windows, right-click its sheet tab, choose Move or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.