Columns
To split a combined cell in Excel, put TEXTSPLIT in a separate empty destination; use Text to Columns for a one-time split. Use TEXTSPLIT in Microsoft 365 or Excel 2024 on Windows or Mac. Keep the source cell unchanged and make room for every output column. For a different task, hide or unhide whole columns with the column-heading menu, or join values with & and TEXTJOIN. To unhide column A in Excel for Windows, type A1 in the Name Box, press Enter, then choose Home > Format > Hide & Unhide > Unhide Columns.
The formula
C2: =TEXTSPLIT(A2, ", ")exact comma-space delimiter; result occupies C2:D2E2: =C2&", "&D2rejoins the example as Doe, JaneE2: =TEXTJOIN(", ",TRUE,C2:D2)alternative join; skips empty cellsA worked example
A2 contains Doe, Jane. C2:E2 are empty, unmerged cells in an ordinary worksheet range, outside an Excel table. The delimiter is the two-character sequence comma followed by one space.
In C2 enter =TEXTSPLIT(A2, ", "). Leave D2 empty for the second result.
C2 shows Doe and D2 shows Jane; A2 still contains Doe, Jane. Do not put this formula in A2, because it would refer to itself and replace the source. To make the same static split in Excel for Windows, select A2 > Data > Text to Columns > Delimited > Next, select Comma and Space, check Treat consecutive delimiters as one, and inspect the two-column preview, then Next > Destination $C$2 > Finish. Use this alternative on a fresh copy with C2:D2 empty. It leaves A2 unchanged; later source edits do not update these static results. This delimiter choice is for the stated example, not a rule for names containing additional spaces.
Which one do I need?
| If you want to… | Use |
|---|---|
| A column is hidden | Select its adjacent column headings, right-click > Unhide. For column A on Windows, select A1 with the Name Box, then Home > Format > Hide & Unhide > Unhide Columns |
| One cell needs a live split | Enter TEXTSPLIT in a separate empty output cell; use Microsoft 365 or Excel 2024 on Windows or Mac |
| A one-time split is enough, including older Windows Excel versions | Data > Text to Columns, inspect the delimiter preview, and set Destination to empty columns away from the source |
| Two columns need joining with a separator | Enter =C2&", "&D2 in E2, outside the source columns |
| Several columns need joining while skipping empty cells | Enter =TEXTJOIN(", ",TRUE,C2:D2) in an empty cell outside C2:D2 |
Frequently asked questions
How do I unhide column A specifically?
In Excel for Windows, type A1 in the Name Box and press Enter, then Home > Format > Hide & Unhide > Unhide Columns. The current Microsoft web instructions instead say to right-click the column B heading and choose Unhide Columns. These are different menu paths; the Windows path is not presented as a Mac shortcut.
Does TEXTSPLIT require Microsoft 365?
Microsoft's current TEXTSPLIT page also lists Excel 2024 and Excel 2024 for Mac, alongside Microsoft 365 for Windows and Mac. For Excel 2021 or 2019 on Windows, use the documented Text to Columns wizard instead. The live formula and static wizard are alternatives, not steps to run over the same output.
Will Text to Columns overwrite my source or nearby data?
It writes static values at the selected Destination. In this example, choosing $C$2 with C2:D2 empty preserves A2. A destination over the source replaces it, and occupied output cells put nearby data at risk. Work on a copy, inspect the preview and choose enough empty output columns before Finish.
How do I fix #SPILL! without losing existing data?
Select the formula cell and inspect the indicated spill range or Select Obstructing Cells. Move the formula to a sufficiently large empty, unmerged range rather than clearing valuable cells. Spilled array formulas must sit outside Excel tables. An occupied cell is one possible cause of #SPILL!, not its only cause.
Function references
New guides and tools, once a month
DE + EN · double opt-in · no spam