Split Cell Contents Into Multiple Columns in Excel

Yes. In Excel for Microsoft 365 on Windows, select the cells and choose Data > Text to Columns to split their contents into separate columns. Choose a delimiter such as a comma or space, or mark fixed-width breaks; for a repeatable or formula-driven split, use a formula or Flash Fill instead.

What it means to split cell contents

A spreadsheet cell holds one value, but that value can contain several pieces of information. For example, a cell might contain the text Jordan Lee,Seattle,WA. Splitting it at each comma places the name, city, and state in adjacent columns. The operation separates the contents of cells in a column; it does not divide a cell into smaller spreadsheet cells or change the worksheet’s grid.

This distinction matters because Excel also has merged cells, which visually span multiple columns. Unmerging a cell is not the same as splitting its text. If a cell contains one long string, use a text-splitting method. If several cells were merged into one display area, use Home > Merge & Center to unmerge them, then decide where to put the values.

Splitting is useful for imported contact lists, addresses, product codes, and data copied from websites. It can make a column easier to sort or filter, but only when the pieces have a consistent pattern. A comma-separated list is usually straightforward; a full address with optional apartment numbers and inconsistent punctuation may need additional cleanup.

Choose a method for your data

Text to Columns is usually the simplest choice for a one-time cleanup. It replaces the selected source column’s contents with the split results unless you choose a different destination. A formula is a better fit when the source should remain intact or when new rows need to be split automatically. Flash Fill can recognize a pattern from examples, and Power Query is useful when the same import-and-clean process will be repeated.

Use Text to Columns for a quick, permanent split based on a delimiter or character position.
Use TEXTSPLIT when you want results that update as the source changes. It is available in Microsoft 365 and Excel 2024.
Use Flash Fill when you can show Excel the output pattern and want values rather than formulas.
Use Power Query for recurring imports or a sequence of transformations you want to refresh.

Before choosing, check what separates the pieces and whether the same number of pieces appears in every row. Also check whether the output columns are available. Any method that fills adjacent cells can replace data that is already there.

Split with Text to Columns

For a basic split, select the source cells, including the header only if you want Excel to process it too. Open Data > Text to Columns. The Convert Text to Columns Wizard lets you choose Delimited or Fixed width. The former separates text at characters such as commas; the latter separates it at positions you specify.

Choose Delimited for values separated by punctuation, tabs, or spaces, then select Next.
Choose the delimiter that appears in your data. The preview shows where Excel plans to break the text. Select Next.
Review the column preview and, if needed, select an output column and choose a data format such as Text or a date format.
Set the Destination if you want the results somewhere other than the source column, then select Finish.

For example, selecting a range containing Jordan,Lee,Seattle and choosing comma as the delimiter produces Jordan, Lee, and Seattle in three adjacent columns. The original column is replaced by the first result column when you keep the default destination. If you need to retain the original, set the destination to an empty area, such as another worksheet or a blank column.

In the wizard, the delimiter options include Tab, Semicolon, Comma, Space, and Other, where you can type a character. The preview is important: use it to confirm that each break falls in the right place before finishing. Excel for Mac also provides Text to Columns from the Data tab, although the wizard’s appearance and ribbon layout can differ slightly. The resulting columns and delimiter choices serve the same purpose.

Delimiters, fixed widths, and uneven data

A delimiter is a character or marker that indicates a boundary. Commas are common in exported data, while tabs often separate fields copied from another table. If a file uses a vertical bar, colon, or another uncommon character, choose Other and enter it. Do not select a delimiter just because it appears somewhere in the text: the preview should show the intended number of fields in each row.

Spaces can be tricky. Splitting Jordan Lee at every space produces two fields, which may be useful for a simple first-and-last-name list. But a name such as Mary Ann Smith produces three. Likewise, a full address may contain spaces within the street name and city. If the pieces are not consistently positioned, Text to Columns cannot infer which words belong together. Consider splitting on a more reliable marker, using a formula with a carefully chosen rule, or cleaning the data manually.

For fixed-width text, select Fixed width in the wizard and use the preview to place or move break lines at the character positions where fields end. This is useful for older reports that align values in columns with spaces rather than delimiters. It is less reliable if characters vary in length, such as names, because a break at one position can cut through a longer entry.

Repeated delimiters can create empty fields. In the wizard, the option to treat consecutive delimiters as one can help when repeated separators represent a single boundary. Use it only if empty fields are not meaningful. For example, in North,,West, the doubled comma might intentionally indicate a missing middle value; treating consecutive commas as one would remove that blank position.

Split with a formula that updates

In Microsoft 365 or Excel 2024, TEXTSPLIT returns the parts of a text value into neighboring cells. With the full text in A2 and commas between its fields, enter this in B2:

=TEXTSPLIT(A2,",")

For that example, if A2 contains Jordan,Lee,Seattle, the formula spills Jordan, Lee, and Seattle horizontally across B2:D2. Keep those spill cells empty; otherwise Excel may show a #SPILL! error. The formula result changes when A2 changes, unlike the permanent output from Text to Columns.

If the source uses a semicolon, replace the comma delimiter with a semicolon. To split on a space while ignoring repeated spaces, and with the text still in A2, use this formula in B2:

=TEXTSPLIT(A2," ",,TRUE)

The omitted third argument leaves the row delimiter unset; TRUE in the fourth argument tells the function to ignore empty pieces created by repeated spaces. Check this carefully if empty fields have meaning in your data. TEXTSPLIT can also use a row delimiter when you want results arranged vertically, but for ordinary column splitting, the column delimiter is the argument most people need.

Dynamic-array formulas are not available in every older Excel release. If TEXTSPLIT is not recognized, use Text to Columns, Flash Fill, or a version of Excel that supports the function. Also remember that a formula producing text does not necessarily preserve the original cell’s data type. If a split field is an identifier with leading zeros, format or handle it as text rather than assuming it is a number.

Use Flash Fill for recognizable patterns

Flash Fill can extract or combine information by learning from examples. Suppose A2 contains Jordan Lee. Enter Jordan in B2, then begin typing the next first name in B3. If Excel recognizes a consistent pattern, it may preview the remaining first names. Choose Data > Flash Fill or press Ctrl+E on Windows to apply the suggestions. On Mac, use Data > Flash Fill if the command is available in your Excel ribbon; keyboard shortcuts can differ by keyboard and configuration.

To split both first and last names, make separate example columns and provide an example for each output. Review the results, especially for middle names, hyphenated names, blank rows, and inconsistent spacing. Flash Fill creates values, not a formula linked to the source, so later edits to the original text do not automatically update the filled results. It is helpful for an irregular but recognizable pattern, not a substitute for validating data.

Protect data and fix common problems

The most important precaution is to inspect the destination. Text to Columns writes across columns, so existing values to the right can be overwritten. Choose a blank destination or copy the source to another location first. Excel’s wizard may warn about replacing data, but planning for the output width is safer than relying on a warning.

Leading zeros disappear: codes such as 00428 may be interpreted as numbers. In the wizard’s final step, select the relevant output column and set its format to Text before finishing.
Dates change meaning: a value such as 03/04/2026 can be interpreted differently under different date conventions. Choose the matching date format in the wizard or import the value as text when its meaning is uncertain.
Extra columns appear: a delimiter may also occur inside a field. Check the preview and choose a less ambiguous delimiter or another method.
Formula shows #SPILL!: clear cells in the intended output area and check for merged cells blocking the result.
Some rows split differently: the source may contain inconsistent delimiters, missing values, or extra spaces. Clean or standardize the source before splitting, or inspect the affected rows individually.

If an operation gives an unexpected result, Undo (Ctrl+Z on Windows; Command+Z on Mac) can reverse a recent worksheet change. For a high-impact cleanup, work on a copy first. Text to Columns is not a reversible link to the original data; after saving and continuing other work, restoring the source may require a backup or re-import.

When Power Query is a better alternative

If the same file arrives every week or month, Power Query can make the split part of a refreshable process. In Excel for Microsoft 365 on Windows, import the file through Data > From Text/CSV and select Transform Data to open the Power Query Editor. Select the column, then choose Transform > Split Column > By Delimiter. Choose the delimiter and whether to split at each occurrence or at a specified occurrence, review the preview, and load the transformed result back to Excel.

Power Query is useful when splitting is only one step among several, such as removing blank rows, changing data types, and renaming columns. After the query is set up, refreshing it can apply the recorded steps to an updated source. It has more setup than a one-time Text to Columns operation, and the editor’s controls differ somewhat on Mac and across Excel builds, so use the visible Transform and Split Column commands in the installed version. For a small list that will not be refreshed, the wizard is usually quicker.

Frequently Asked Questions

Can I split one cell into two columns in Excel?

Yes. Select the cell or column, choose Data > Text to Columns, and select the delimiter or fixed-width break that separates the two parts. If you want to keep the source unchanged, choose a different empty destination or use a formula.

How do I split a full name into first and last name?

For consistently two-part names separated by a space, use Text to Columns with Space as the delimiter or use Flash Fill after entering examples. Names with middle names, compound surnames, or inconsistent formats need review because a single space does not always identify the boundary between first and last name.

Can I split text without changing the original cell?

Yes. Set a separate destination in the Text to Columns wizard, or enter a TEXTSPLIT formula in a blank cell. A formula keeps the source value and updates its result when the source changes; Text to Columns creates separate values at the chosen destination.

Why is Excel putting the results over my existing data?

Text to Columns outputs across adjacent columns, starting at the destination you specify. If that area is occupied, the results may replace it. Undo if the change just happened, then rerun the split with a blank destination wide enough for all the output fields.

Can I split text into rows instead of columns?

Yes. In the Text to Columns wizard, split into columns first, then copy the results and use Paste Special > Transpose to turn columns into rows. In Microsoft 365 or Excel 2024, TEXTSPLIT also supports a row delimiter for vertical results. Check the output range before using either approach.

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.