Excel Text to Columns Formula: Split Text in Cells

Formulas & functions · Updated November 21, 2026

Excel does not have a worksheet function called Text to Columns: the command is a data-splitting tool, not a formula. If the text to split is in A2 and you have Microsoft 365 or Excel 2024, use =TEXTSPLIT(A2,", ") to split at each comma followed by a space; use Data > Text to Columns when you want Excel to split the existing data directly.

What “Text to Columns” means in Excel

Text to Columns is a command on Excel’s Data tab that separates the contents of one column into multiple columns. It is useful for cleaning imported lists, separating first and last names, dividing addresses, or splitting codes that have a consistent pattern. The command does not calculate a result in a worksheet cell, so you cannot enter “Text to Columns” as a formula.

There are two common ways to get a similar result. Use the Text to Columns wizard to convert the selected cells into separate values. Or use a formula such as TEXTSPLIT to return the parts in other cells. The best choice depends on whether the split should be a one-time change or should update whenever the original text changes.

Split text with the TEXTSPLIT formula

TEXTSPLIT is available in Microsoft 365 and Excel 2024. It can split text using a column delimiter, a row delimiter, or both. For a simple split, the first argument is the cell containing the text and the second is the delimiter. Excel returns the pieces in neighboring cells as a dynamic array.

With the full name and location in A2 as “Mira Chen, Seattle, WA,” enter this formula in B2:

=TEXTSPLIT(A2,", ")

The result spills across B2:D2 as “Mira Chen,” “Seattle,” and “WA.” The comma and the following space form one delimiter, so neither character appears in the results. If the source is in A2, leave enough empty cells to the right for all the parts; otherwise, Excel can return a #SPILL! error.

TEXTSPLIT can also separate items down rows. With a list in A2 that reads “red;green;blue,” enter this in B2:

=TEXTSPLIT(A2,,";")

The result spills down B2:B4 as red, green, and blue. In this formula, the omitted second argument means there is no column delimiter, and the semicolon is supplied as the row delimiter. This can be handy when one cell contains a list that should become a vertical list.

Choose the right delimiter

The delimiter is the character or text sequence that marks where one item ends and the next begins. A comma is common in exported data, but tabs, semicolons, spaces, slashes, hyphens, and custom strings can also be delimiters. Check a few source cells before choosing one: a delimiter that also occurs inside a value can split that value in the wrong place.

For example, splitting “Chen, Mira” at every comma is suitable if the goal is to put the last and first names in separate cells. It is not suitable if commas are part of a quoted address or a company name. TEXTSPLIT treats the delimiter literally; it does not automatically interpret comma-separated files’ quotation rules. In that situation, importing the file through Excel’s data import tools may be more reliable.

When using TEXTSPLIT with inconsistent spacing, you can ask it to ignore empty results. With A2 containing “Mira Chen” (two spaces between the names), enter this in B2:

=TEXTSPLIT(A2," ",,TRUE)

The formula treats a space as the delimiter and ignores empty results caused by consecutive spaces, returning “Mira” and “Chen.” The fourth argument, TRUE, controls whether empty pieces are ignored. This is useful for repeated delimiters, but it can also remove intentional blank fields, so use it only when that behavior is wanted.

Use the Text to Columns wizard

For a one-time split in Excel for Microsoft 365 on Windows, select the source cells and choose Data > Text to Columns. In the wizard, choose Delimited if a character separates the values, or Fixed width if the values line up at specific positions. Follow the preview and destination options before finishing.

  1. Select the cells in the column you want to split. Leave adjacent columns empty if you want to preserve their contents.
  2. Choose Data > Text to Columns, then select Delimited or Fixed width.
  3. For Delimited, select the relevant delimiter, such as Tab, Comma, Space, or Other. Use the preview to check where Excel will divide the text.
  4. Choose a destination if you do not want the output to begin in the source column, review any column formats, and select Finish.

Delimited is usually the right choice for values like “Mira Chen, Seattle,” where punctuation marks the boundaries. Fixed width is useful for records such as “0042 20261103 NORTH,” if each field always occupies the same character positions. In the fixed-width preview, click to add a break line or move a break to adjust the split.

Take care with the destination. If the output will occupy cells that already contain data, Excel may overwrite those cells. Selecting a separate empty destination column is a safer approach when the neighboring columns matter. The wizard changes worksheet values; unlike a formula, its results do not automatically update if you later edit the original text.

Text to Columns versus a formula

The wizard and a formula can produce similar-looking results, but they serve different purposes. Text to Columns is convenient when cleaning a column once and keeping the resulting pieces as ordinary cell values. A formula is preferable when the original data may change, when you want the split to remain linked to its source, or when you need the result to update as new text is entered.

  • Choose the wizard for a quick, one-time conversion, for fixed-width records, or when you want to set formats during the split.
  • Choose TEXTSPLIT for a repeatable result, a formula-driven worksheet, or a range of source cells you expect to refresh.
  • Choose an older formula method if the workbook must work in an Excel version without TEXTSPLIT and the split pattern is simple and predictable.

TEXTSPLIT returns a dynamic array, so a single formula can fill several cells. Do not type into the spill area: doing so blocks the result. The formula itself remains in the top-left cell, and the other displayed results are generated by Excel. If you need fixed values for sharing or further processing, copy the results and use Paste Special > Values.

Alternatives for older Excel versions

In versions without TEXTSPLIT, a formula can extract a particular part when the delimiter pattern is simple. These examples assume a comma separates two fields and there is no other comma before the field you want. With “Mira Chen, Seattle” in A2, this formula in B2 returns the text before the first comma:

=LEFT(A2,FIND(",",A2)-1)

With the same value in A2, this formula in C2 returns the text after the first comma and removes the extra space at the beginning:

=TRIM(MID(A2,FIND(",",A2)+1,LEN(A2)))

These formulas use LEFT, FIND, MID, LEN, and TRIM, which are available in older Excel versions. They are more limited than TEXTSPLIT: each formula targets a particular portion, and splitting a variable number of fields requires additional logic or repeated formulas. If a delimiter might be missing, FIND returns an error, so you may need to check the source data or add error handling.

Another built-in option for a one-time operation is the wizard itself, which is available in desktop Excel versions that include Text to Columns. Excel for Mac also provides a Text to Columns command on the Data tab, though the dialog and ribbon layout can differ by version. Excel for the web can use supported worksheet formulas such as TEXTSPLIT, but if you do not see the wizard in your web interface, open the workbook in desktop Excel for that command.

Common problems and how to fix them

  • The formula returns #SPILL!: Clear cells in the area where the dynamic array needs to appear. Merged cells or an Excel table can also prevent a spill in some situations; move the formula to a clear area or use the wizard.
  • The results contain blank cells: Repeated delimiters create empty pieces. If those blanks are unwanted, TEXTSPLIT’s ignore-empty argument may help. For the wizard, check whether consecutive delimiters should be treated as one.
  • A field splits in the wrong place: Check whether the delimiter occurs inside the data. A plain delimiter split does not know which commas are punctuation and which are field separators.
  • Leading zeros disappear: Excel may interpret a split value such as 0042 as a number and display it as 42. In Text to Columns, set the relevant column’s format to Text before finishing, or preserve the value as text through your formula or import process.
  • Dates change format: Excel may recognize split text as dates and display them according to the cell’s number format. Review the wizard’s column format choices or format the destination cells deliberately.
  • Nothing splits at a space: A normal space, a nonbreaking space, and a tab are different characters. Imported text may contain a tab or nonbreaking space where a visible space appears; inspect or clean the source before splitting.

Also remember that splitting is not the same as parsing. If a source file encloses fields in quotation marks, contains escaped quotation marks, or uses a complex regional format, a simple delimiter formula may not follow the file’s rules. Use Excel’s import process for structured files such as CSV when quotation and regional settings affect how fields should be read.

Frequently Asked Questions

Is there a Text to Columns formula in Excel?

No. Text to Columns is a Data tab command rather than a worksheet function. TEXTSPLIT is the closest modern formula for separating text into multiple cells, and it is available in Microsoft 365 and Excel 2024.

What is the formula to split text by a comma?

With the text in A2 and a comma as the delimiter, enter =TEXTSPLIT(A2,",") in an empty cell. It returns the portions on either side of each comma, spilling into neighboring cells. Add a space to the delimiter, as in comma-space, if that exact sequence separates your values.

Can Text to Columns split text into rows?

The Text to Columns wizard is designed to place the separated fields in columns. With TEXTSPLIT, provide a row delimiter instead: for text in A2 separated by semicolons, =TEXTSPLIT(A2,,";") returns the items down a column.

Why does TEXTSPLIT show #NAME?

That error commonly means your Excel version does not recognize the function. TEXTSPLIT requires Microsoft 365 or Excel 2024. If your installation does not include it, use Text to Columns or build a suitable formula with older functions such as LEFT, FIND, MID, and TRIM.

Does Text to Columns keep the original text?

The wizard writes split results into worksheet cells and can replace the source contents when the destination overlaps the source. Choose an empty destination to retain the original column. A TEXTSPLIT formula leaves its source cell unchanged, but its spilled results still need clear cells around the formula.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.