Excel Text to Columns by Word: Methods That Work

To split text at spaces between words, select the cells and choose Data > Text to Columns > Delimited, then select Space and finish the wizard. To split at a specific word or phrase, use TEXTSPLIT in Microsoft 365 or Excel 2024; Text to Columns accepts a single-character delimiter, not a whole word or phrase.

What “split by word” can mean in Excel

The phrase “text to columns based on word” can describe two different jobs. You might want to put every word in a cell of its own, as when separating a list of full names. Or you might want to split a line at a particular word, such as dividing “Order 481 shipped Monday” at “shipped.” The right method depends on which result you need.

Excel’s Text to Columns tool is built for splitting data at delimiters—characters such as spaces, commas, or tabs—or at fixed character positions. It does not search for an arbitrary word as a delimiter. A space is a one-character delimiter, so the tool can split on spaces. A word such as “shipped,” or a phrase such as “ by ”, is multiple characters and needs a formula or a workaround.

Also decide whether the delimiter should disappear from the result. Text to Columns removes the delimiter when it divides the data. TEXTSPLIT does the same. If the word you split around needs to remain in one of the resulting pieces, use a different formula or plan to add it back.

Split a cell at every space with Text to Columns

Use this method when each word should occupy a separate column. For example, splitting “Maya Chen” at the space produces “Maya” and “Chen.” The tool works on selected cells and writes the pieces horizontally across the row; it does not create a new row for every word.

Make a copy of the source column or choose an empty destination. Text to Columns can overwrite cells to the right of the selected data.
Select the cells you want to split. If the data has a heading, you can select the data cells without the heading.
On the Windows desktop, choose Data > Text to Columns.
In the wizard, choose Delimited, then select Next.
Select Space as the delimiter. Check the preview to confirm that the breaks fall in the right places.
If multiple spaces should count as one break, select Treat consecutive delimiters as one. Choose Next to review column formats and the destination, then select Finish.

For instance, “Maya Chen” becomes two columns, while “Maya Chen” (with two spaces) can produce an empty column between the names unless consecutive delimiters are treated as one. The preview is useful for spotting this before Excel changes the worksheet.

Text to Columns is available from the Data tab in desktop Excel for Microsoft 365 on Windows. On Mac, use the Data tab’s Text to Columns command as well; dialog appearance and some labels can differ slightly by release. If the command is unavailable in your Excel for the web interface, open the workbook in the desktop app or use a supported formula.

Split at a specific word or phrase with TEXTSPLIT

In Microsoft 365 or Excel 2024, TEXTSPLIT can use a whole word or phrase as its delimiter. This is often the clearest option when the boundary is something more specific than a space. The formula returns the pieces in adjacent cells, provided those cells are empty.

With the complete text “Order 481 shipped Monday” in A2, enter =TEXTSPLIT(A2," shipped ") in B2. The result spills across the row as “Order 481” in B2 and “Monday” in C2. The spaces around “shipped” are part of the delimiter, so they are removed along with the word. If you instead use =TEXTSPLIT(A2,"shipped"), the results will retain the spaces on either side of the split.

The delimiter must match the text you want Excel to find. A delimiter of " by " includes a space before and after “by,” which helps avoid splitting a longer word such as “bypass.” Matching is case-insensitive by default. You can use the optional match-mode argument to request case-sensitive matching, but most ordinary text cleanup does not require it.

To split a sentence at every ordinary space with a formula, put the source text in A2 and enter =TEXTSPLIT(A2," ") in B2. If repeated spaces create unwanted empty results, enter =TEXTSPLIT(A2," ",,TRUE) instead; the fourth argument tells Excel to ignore empty pieces. These formulas require Microsoft 365 or Excel 2024. They are not available in perpetual versions such as Excel 2021.

Keep or remove the word used as the boundary

Both Text to Columns and TEXTSPLIT treat a delimiter as the dividing boundary, not as part of either result. If splitting “Order 481 shipped Monday” at “shipped” should leave that word attached to the first piece, you can use TEXTBEFORE and TEXTAFTER to control the two outputs.

With “Order 481 shipped Monday” in A2, enter =TEXTBEFORE(A2," shipped") in B2 to return “Order 481,” and enter =TEXTAFTER(A2,"shipped ") in C2 to return “Monday.” To keep the boundary word, include it in the text returned by the formula: with the same A2 value, =TEXTBEFORE(A2," shipped")&" shipped" in B2 returns “Order 481 shipped.” TEXTBEFORE and TEXTAFTER are available in Microsoft 365 and Excel 2024.

These functions are handy when a phrase marks a single boundary and you want separate control over each side. If the boundary can occur more than once and every occurrence should produce another column, TEXTSPLIT is generally more suitable. Check your data for variations such as “Shipped,” “shipped,” and “ shipped ” before choosing the exact delimiter.

Use a one-character workaround in older Excel

If your Excel version does not have TEXTSPLIT, you can replace a distinctive word or phrase with a one-character marker, then split on that marker with Text to Columns. This works because the wizard’s Other delimiter option is intended for a character, not a multi-character phrase.

With “Order 481 shipped Monday” in A2, enter =SUBSTITUTE(A2," shipped ","|") in B2. The helper cell returns “Order 481|Monday.” Copy the helper results and, if you want fixed values, use Paste Special > Values before splitting. Select those helper cells, choose Data > Text to Columns, select Delimited, choose Other, type | as the delimiter, and finish the wizard. The result is “Order 481” and “Monday.”

Choose a marker that does not already appear in the source text. If a pipe character could occur in your data, another uncommon character may be safer. SUBSTITUTE replaces every matching occurrence and is case-sensitive, so it will not replace “Shipped” when the specified text is lowercase “shipped.” Preserve a copy of the original values until you have checked the output.

Choose the method that fits the data

Text to Columns is a good fit for a one-time cleanup of a conventional list. It is quick to preview and does not require a formula. Its main limitation is that it changes the selected data into separate cell values. If the source changes later, you need to run the operation again.

A formula is more useful when the source text may be updated, when the delimiter is a word or phrase, or when you want the output to update automatically. Dynamic array formulas such as TEXTSPLIT spill results into neighboring cells; those cells must be clear, or Excel reports a spill error. Formulas also keep a link to the original cell, which can be helpful for repeatable reports.

Split on spaces once: use Text to Columns with Space.
Split on a particular word or phrase: use TEXTSPLIT in Microsoft 365 or Excel 2024.
Use an older Excel version: replace the phrase with a unique one-character marker, then use Text to Columns.
Separate just the text before or after one boundary: consider TEXTBEFORE and TEXTAFTER in Microsoft 365 or Excel 2024.

If you need one word per row rather than one word per column, Text to Columns is not the final step: it lays results across columns. You can transpose the results afterward, or use a formula-based approach suited to the required layout. Decide on the output shape before transforming a large list.

Common problems and data checks

Columns are overwritten. Text to Columns writes into the selected column and the cells to its right. If those cells contain other data, Excel may warn you or the results may replace existing content. Set the wizard’s destination to a blank area, or make a copy of the worksheet first.

Names or phrases split in the wrong place. A space delimiter splits at every space, including spaces inside multiword surnames, addresses, or product descriptions. If “Ana de Souza” should stay together, splitting every space may not match the intended structure. A specific phrase delimiter or a more tailored cleanup is safer.

Leading zeros disappear. Text to Columns may interpret a piece such as “0042” as a number and display it as 42. In the wizard’s final step, select the affected column in the preview and choose the Text column data format. This is important for postal codes, account identifiers, and codes that look numeric but are not quantities.

Dates or numbers change format. Excel may interpret split values as dates or numbers according to their content and regional settings. Review the preview and the results. Choose Text for values that must remain literal strings, or choose the appropriate date format when the values really are dates.

A formula does not split. Check whether the delimiter in the formula matches the source exactly, including spaces and punctuation. For example, a nonbreaking space pasted from a web page may look like an ordinary space but behave differently. Also check for blocked spill cells, and confirm that your Excel release supports the function you used.

Frequently Asked Questions

Can Text to Columns split on a whole word?
Not directly. Its Delimited option uses individual delimiter characters, including a space or a character entered under Other. Use TEXTSPLIT for a multi-character word or phrase in Microsoft 365 or Excel 2024, or replace that phrase with a unique character before using the wizard.

How do I put each word in a separate column?
Select the cells, choose Data > Text to Columns, select Delimited, and choose Space. Review the preview and select Treat consecutive delimiters as one if repeated spaces should not create empty columns.

Can I split text into rows instead of columns?
Text to Columns places pieces horizontally across columns. It does not turn each word into a separate row. Use a suitable formula or transpose the resulting columns if a vertical list is required.

Why does Text to Columns remove the word or space?
The selected delimiter marks where Excel divides the text, so it is not included in either output. If you need to retain the boundary word, use TEXTBEFORE or TEXTAFTER and add the word back to the appropriate result.

Does TEXTSPLIT work in Excel 2021?
No. TEXTSPLIT requires Microsoft 365 or Excel 2024. In older versions, use Text to Columns for a one-character delimiter or use the SUBSTITUTE-and-marker workaround for a word or phrase.

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.