To split text at a space in Excel for Microsoft 365 on Windows, select the cells and choose Data > Text to Columns > Delimited, select Space, then finish the wizard. For a formula-based split that updates when the source changes, use =TEXTSPLIT(A2," ") in a blank cell; it spills the pieces into adjacent columns. Excel does not divide one cell into smaller cells: it puts the split results into separate cells.
What “split a cell” means in Excel
A worksheet cell holds one value. Splitting its contents means separating that value at a chosen character—here, a space—and placing each resulting piece in another cell. For example, splitting “Maya Chen” at its space produces “Maya” and “Chen” in neighboring columns. The source can be replaced by the results or kept separately, depending on the method and destination you choose.
A space is a useful delimiter for names, addresses, product labels, and other text, but it is not always a reliable indicator of meaning. A two-part name may split neatly, while a three-part name such as “Maya Li Chen” produces three pieces. If you need first name and everything else, rather than one column per word, choose a first-space formula or a left-most-delimiter option instead of splitting at every space.
Split at spaces with Text to Columns
Text to Columns is a practical choice for a one-time cleanup or a fixed list. These steps are for Excel for Microsoft 365 on Windows:
- Select the cells containing the text. If you select a whole column, check that the cells to its right are empty or that you have room for the results.
- Open the Data tab and choose Data > Text to Columns.
- In the Convert Text to Columns Wizard, select Delimited and choose Next.
- Check Space as the delimiter. The preview shows where Excel will divide the text. If repeated spaces should count as a single separator, check Treat consecutive delimiters as one.
- Choose Next if you want to set column formats, or choose Finish to complete the split.
By default, the operation writes the separated values into columns starting at the selected range. To preserve the original text, set a different destination in the wizard, such as B2, before finishing; otherwise, the results may replace the source values. Also verify that neighboring cells are clear, because a split can overwrite existing content in the destination area.
In Excel for Mac, the Text to Columns command is also available from the Data tab, though the wizard’s appearance can vary by release. Excel for the web may not offer the same desktop wizard options in every account or build; formulas are a dependable alternative when the command is unavailable. Text to Columns changes the stored data, so it does not automatically redo the split if you later edit the original text.
Use TEXTSPLIT for a formula that updates
In Microsoft 365 or Excel 2024, TEXTSPLIT can divide text at each occurrence of a delimiter. With the text in A2, enter this formula in B2:
=TEXTSPLIT(A2," ")
For the example “Maya Chen,” the result is “Maya” in B2 and “Chen” in C2. The formula spills into neighboring cells, so those cells must be empty. If the value in A2 changes, the displayed result updates automatically. Copy the formula down for more source rows; each row’s results spill across from its own formula cell.
TEXTSPLIT separates at every space, so “Maya Li Chen” returns three pieces. To split only at the first space and keep the rest together, use TEXTBEFORE and TEXTAFTER instead. With the complete text in A2, enter these formulas in B2 and C2 respectively:
=IFERROR(TEXTBEFORE(A2," "),A2)returns “Maya” for “Maya Li Chen,” or the whole value if there is no space.=IFERROR(TEXTAFTER(A2," "),"")returns “Li Chen,” or a blank if there is no space.
TEXTBEFORE, TEXTAFTER, and TEXTSPLIT are available in Microsoft 365 and Excel 2024. Older Excel releases may not recognize these functions, so use Text to Columns or the compatible formulas below instead. Formula results are not independent values until you copy them and use Paste Special > Values; this matters if you plan to delete the original column or send a static export.
Handle repeated spaces and irregular text
Real-world text often contains extra spaces at the beginning or end, or multiple spaces between words. A plain TEXTSPLIT formula treats each space as a delimiter, and consecutive spaces can create empty results between words. For ordinary spaces, TRIM removes leading and trailing spaces and reduces repeated internal spaces to one. With the source text in A2, this version also tells TEXTSPLIT to ignore empty pieces:
=TEXTSPLIT(TRIM(A2)," ",,TRUE)
The blank third argument leaves the row delimiter unspecified; TRUE in the fourth argument sets the ignore-empty option. For example, a source value with extra spaces around “Maya” and “Chen” still produces the two words rather than blank columns between them.
Some imported text uses a nonbreaking space, which looks like a normal space but is a different character. TRIM may not remove it. If that is the problem, replace character 160 with a regular space before splitting. With the text in A2, use:
=TEXTSPLIT(TRIM(SUBSTITUTE(A2,CHAR(160)," "))," ",,TRUE)
This approach addresses nonbreaking spaces specifically; it does not clean every possible hidden character. If a result still looks wrong, inspect a few source cells and consider whether the delimiter is a tab, line break, comma, or another character rather than an ordinary space.
Use older formulas for a first-space split
If your Excel version does not support TEXTSPLIT, LEFT, FIND, MID, LEN, and TRIM can separate a value at its first space. With the text in A2, place the first formula in B2 and the second in C2:
=LEFT(A2,FIND(" ",A2&" ")-1)returns the text before the first space.=TRIM(MID(A2,FIND(" ",A2&" ")+1,LEN(A2)))returns the remaining text after the first space.
For “Maya Li Chen,” these formulas return “Maya” and “Li Chen.” Appending a space inside FIND ensures the search can find a delimiter even when the original value has no space. In that case, the first formula returns the entire value and the second returns a blank. These are first-space formulas, not a way to generate a separate column for every word.
Choose the method that fits the task
- Text to Columns: Best for a one-time conversion when you want to replace or relocate the original text. It is easy to preview, but the output does not update automatically.
- TEXTSPLIT: Best for a changing source list when you need each space-separated piece in its own column. It requires Microsoft 365 or Excel 2024 and clear cells for the spill range.
- TEXTBEFORE and TEXTAFTER: Best when only the first space matters, such as separating a first name from the remaining name. These functions also require Microsoft 365 or Excel 2024.
- Power Query: Best for repeatable imports or larger datasets that you refresh after receiving new source data.
Think about the shape of your data before choosing. Splitting every space in a street address can put the street number, street name, and suffix in separate columns, which may not be what you intend. Conversely, splitting a fixed list of two-part labels into exactly two columns may be simpler with Text to Columns than with a formula.
Split by space with Power Query
Power Query is useful when the same cleanup needs to be repeated on refreshed data. In Excel for Microsoft 365 on Windows, format the source range as a table or select a cell within it, then choose Data > From Table/Range. In the Power Query Editor, select the text column and choose Transform > Split Column > By Delimiter. Set the delimiter to a space and select whether to split at each occurrence or only at the left-most delimiter. Confirm the operation, then choose Home > Close & Load to return the result to Excel.
Splitting at each occurrence creates a column for each segment, while splitting at the left-most delimiter keeps everything after the first space together. Power Query records the transformation steps, so when the source table is updated, you can refresh the query to apply the same split again. The source data is not edited in place in the worksheet; the query produces a loaded result. Power Query is available in desktop Excel, but available connectors and interface details can differ on Mac and in Excel for the web.
Common problems and ways to avoid them
- Existing values disappear: Text to Columns or a spilling formula can write into occupied cells. Choose a blank destination or clear the expected output range first.
- The formula shows #SPILL!: One or more cells needed for the result are occupied, merged, or otherwise blocking the spill. Clear the blocking cells and leave enough columns for the longest entry.
- Words appear in unexpected columns: Text to Columns splits at every selected space unless you configure another option. Check the preview and consider splitting only at the first space.
- Extra blank pieces appear: Repeated spaces can act as multiple delimiters. Use Treat consecutive delimiters as one in the wizard or clean the text with TRIM before TEXTSPLIT.
- Some rows split and others do not: The data may contain nonbreaking spaces or other hidden characters. Replace the relevant character, or use a formula such as the SUBSTITUTE example above.
- Names or phrases lose their intended grouping: A space does not identify which words belong together. Decide whether the goal is one column per word, two fields at the first space, or a more specific rule before splitting.
For a safe one-time operation, keep a copy of the original column or work on a duplicate sheet. After splitting, inspect rows with the most words, rows with no space, and any known exceptions. That small check often catches problems that are invisible in a short preview.
Frequently Asked Questions
Can Excel split a cell into two cells?
It can split the text inside a cell and place the pieces in separate cells, but it does not divide the physical cell itself. Use Text to Columns or a formula, and make sure the destination cells are available.
How do I split a full name at the first space?
Use TEXTBEFORE for the first part and TEXTAFTER for the remainder, with IFERROR to handle entries that have no space. For older Excel, use the LEFT/FIND and MID formulas shown above. This keeps middle names or additional words with the remainder.
How do I split text at every space?
In Microsoft 365 or Excel 2024, enter =TEXTSPLIT(A2," ") in a blank cell. To split a fixed range instead, use Data > Text to Columns and select Space as the delimiter.
Why does TEXTSPLIT return empty columns?
Consecutive spaces can produce empty pieces. Wrap the source in TRIM and set TEXTSPLIT’s ignore-empty argument to TRUE, as in =TEXTSPLIT(TRIM(A2)," ",,TRUE). If the spaces are nonbreaking spaces, replace them first.
Will the split update when I edit the source?
Formula results recalculate when the source changes. Text to Columns does not maintain a live connection to the original value; for repeatable imports, Power Query can reapply the split when you refresh the query.
Related
- Excel Scatter Plot Vertical Line: Add One and Format It
- Excel Scatter Plot With Filters: How to Set It Up
- Excel Scatter Plot: How to Swap X and Y Axes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Month and YearIf the date is in A2, enter =TEXT(A2,"mmmm yyyy") in B2 to display its month and year, such…
- Excel Data Table Analysis: Compare What-If ScenariosExcel data table analysis is a What-If Analysis tool that recalculates a formula across a range of one…
- Max Value Between Two Dates in Excel: Formula GuideTo find the largest value recorded between two dates in Excel, use =MAXIFS(C2:C100,A2:A100,">="&E2,A2:A100,"<="&F2). This assumes dates are in…
- How to Copy a Formula in Excel All the Way Down a ColumnTo copy a formula all the way down in Excel, select the cell with the formula and double-click…
- How to Subtract in Excel Using a Formula Step by StepTo subtract in Excel, enter a formula with a minus sign in the cell where you want the…
- Sort List in Excel Formula: SORT, SORTBY, and MoreTo sort a list with a formula in Microsoft Excel for Microsoft 365, use SORT: with values in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.