To split the contents of a cell, select it and use Data > Text to Columns to separate text at a delimiter or a fixed position. Excel cannot physically divide one ordinary cell into two independent cells; instead, you can split its contents across columns, create a visual divider, or unmerge a cell that was combined with others. Choose the method that matches what you want to achieve.
What You’ll Need
- Excel for Microsoft 365 on Windows, or a recent Excel version with the same core features.
- A copy of the workbook or a spare worksheet if you are changing a large range. Text to Columns can overwrite cells to the right of your selection.
- For a content split, decide whether the division should happen at a character count, a space, a comma, or another separator.
Step-by-Step
- Decide what “split in half” means for your sheet. If you need two editable values, split the content into neighboring columns. If you want a line through a cell for appearance, use a border. If the cell looks joined to nearby cells, it may be a merged range that needs to be unmerged. These produce different results: a border does not create another cell, and unmerging does not divide the contents.
- Make room for the results. Before using Text to Columns, check the cells immediately to the right of the selected data. Excel places the separated pieces into those columns, and existing values may be replaced. Insert blank columns or move the neighboring data somewhere safe first. If you are working with only one cell, put the intended results in two empty columns beside it.
- Use Text to Columns when there is a separator or a consistent position. Select the cells containing the text, then choose Data > Text to Columns. In the Convert Text to Columns Wizard, choose Delimited if the pieces are separated by a character such as a space, comma, tab, or hyphen. Choose Fixed width if the split should happen at the same character position in every entry. Select Next to set the details.
- Set the split point. For Delimited, tick the relevant delimiter, or choose Other and enter a character such as a slash. Check the preview to make sure the dividing marks appear in the right places. For Fixed width, click in the preview to add a break line; drag a line to move it, or double-click one to remove it. Fixed width is useful for consistently formatted codes, but not for a list where the first part has different lengths in each row.
- Choose a safe destination and finish. Select Next, review the column data formats if needed, then select the Destination box and specify an empty starting cell if you do not want to replace the original values. For example, if the source is in A2:A20, you could set the destination to B2. Select Finish. Review several output rows, including any with unusual spacing or missing separators.
- For an exact halfway split, use formulas instead. Text to Columns does not automatically split every entry at its midpoint if the text lengths vary. Put the first result in B2 and the second in C2, assuming the original text is in A2. In B2 enter
=LEFT(A2,ROUNDUP(LEN(A2)/2,0)). With ABCD1234 in A2, this returns ABCD. In C2 enter=MID(A2,ROUNDUP(LEN(A2)/2,0)+1,LEN(A2)), which returns 1234 for that same example. Fill both formulas down to process more rows. For an odd number of characters, the first result gets the extra character: ABCDE becomes ABC and DE. The formulas split characters, not words or meaningful parts. - Make a visual divider if you only need the appearance of two halves. Select the cell, then open Home > Borders using the border drop-down. Choose More Borders (or open the Format Cells dialog with Ctrl+1), select a diagonal border in the Border tab, and confirm. A diagonal line can suggest two regions, but both sides still belong to one cell and cannot hold separate values. To position labels in the two areas, you may need text boxes or a different layout rather than treating the cell as two cells.
- Unmerge a cell if it was joined to its neighbors. Select the merged range, then choose Home > Merge & Center > Unmerge Cells. The former range becomes separate cells again, but Excel keeps the content in the upper-left cell; the other cells are usually blank. Enter or distribute the needed values yourself. If Merge & Center is not highlighted, the selection may not be merged.
- Check and preserve the result. Confirm that every output is in the intended column, especially if the data has leading zeros, dates, or text that resembles a number. Save the workbook after checking. If formulas are being used and you need fixed values, copy the results and use Home > Paste > Paste Values in a separate destination area.
Common Mistakes
Expecting a real cell to split into two. Excel’s worksheet grid is made of rectangular cells; there is no command that divides one ordinary cell into two independently addressable halves. Use adjacent columns for separate values, or a diagonal border for a visual effect.
Running Text to Columns without checking the destination. The wizard writes results into the cells to the right. If those cells contain information, it can be overwritten. Move or copy existing content before running the command, and use the Destination field to direct results to empty space.
Choosing the wrong split rule. Delimited works when a character marks the boundary; Fixed width works when the boundary stays at the same position. Neither is a safe choice for inconsistent text unless its preview confirms the intended result. A comma inside quoted text, for instance, may need special handling rather than a simple comma split.
Assuming unmerge redistributes text. Unmerge restores the grid, but it does not divide the original value among the cells. Also avoid merging cells in data tables just to make a heading look centered: merged cells can interfere with sorting, filtering, and selecting ranges.
Tips
Keep the source column until you have checked the split. This makes it easier to compare the original with both outputs and repeat the operation if a delimiter was missed. For a one-time cleanup, Text to Columns is convenient; for a result that should update when the source changes, formulas are more suitable.
Use the preview in the Text to Columns Wizard rather than relying on how a few sample values look in the worksheet. Check the longest and shortest entries, blanks, and any rows with multiple delimiters. If the split is by a space, decide how to handle double spaces or names with more than one space before processing the full list.
Preserve codes and identifiers as text when they may contain leading zeros, such as 00427. In the wizard, select the affected output column in the final step and set its column data format to Text. Otherwise Excel may treat the value as a number and remove the zeros. Keep a backup when the original formatting or data type matters.
On Excel for Mac, the core approach is similar: use the Data tab and the Text to Columns command when available, and use the Format Cells border controls for a diagonal line. Ribbon layout and dialog placement can vary by release. In Excel for the web, some desktop commands or dialog options may be limited; if you cannot find Text to Columns or the border option, open the workbook in the desktop app.
Troubleshooting
The output seems to disappear. It may have been written over by Text to Columns or placed farther right than expected. Press Ctrl+Z immediately to undo the operation, then repeat it with a blank destination. On Mac, use Command+Z. If you have made other changes since then, check the workbook’s version history or a saved copy before attempting recovery.
The text did not split where expected. Reopen the wizard and inspect its preview. Confirm the correct delimiter is selected, or adjust the Fixed width break line. If there is no consistent delimiter or position, use formulas or clean the source values first. Watch for leading or trailing spaces, which can make results look uneven.
The formulas return an error or an unexpected result. Make sure the source cell actually contains text and that the formula refers to the correct row. If a cell is blank, the formulas return blank pieces. The midpoint formulas divide by character count, including spaces; they do not split a phrase into equal numbers of words. If your Excel uses semicolons rather than commas in formulas because of regional settings, replace the argument separators accordingly.
The diagonal border is missing or points the wrong way. Select the cell again and open the full border settings to choose the other diagonal. Check the cell’s fill and border color, as a white line on a white background can be hard to see. Remember that the border is only a visual mark and cannot be used to enter two independent values.
Frequently Asked Questions
Can I split one Excel cell into two actual cells?
No. Excel cannot subdivide one ordinary cell into two separate grid cells. You can put the two pieces in neighboring cells, unmerge an existing range, or use a diagonal border to create the impression of two sections.
How do I split a cell exactly in half by character count?
Use the LEFT and MID formulas shown above, with the source in A2 and results in B2 and C2. The formulas split at the midpoint by character count, counting spaces. For an odd-length value, the first half receives one extra character.
Can Text to Columns split every entry at its own midpoint?
No. Its Delimited and Fixed width options use a chosen separator or fixed position, not each entry’s individual midpoint. Use the formulas for a character-based midpoint, or adjust the data first if the intended boundary depends on meaning.
Will unmerging a cell split its contents between the cells?
No. Unmerging restores the separate cells in the range, but the original content remains in the upper-left cell. Copy or enter the other values after unmerging.
Can I split a cell diagonally and type text in both sides?
A diagonal border does not create two text-entry areas. You can place the value in the cell and use text boxes for a visual layout, but text boxes are objects layered over the sheet, not separate cell contents.
Does Text to Columns work in Excel for the web?
Availability and options can differ from the Windows desktop app. If the command is missing or the wizard does not provide the option you need, open the workbook in Excel for Microsoft 365 on Windows or Mac and run the split there.
Related
- How to Redo in Microsoft Excel: Shortcuts and Menu
- How to Rank in Excel with RANK.EQ
- How to Randomize in Excel: Shuffle Lists and Numbers
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Data Validation Lists: Can They Include Formatting?No. In Excel, a data validation list controls which values users can choose or enter; it does not…
- How to Add a Header to Each Page in Microsoft WordTo add a header to every page in Word, choose Insert > Header, select a layout, type your…
- Microsoft Excel Consolidate Data: A Practical GuideTo consolidate data in Microsoft Excel for Microsoft 365 on Windows, select the destination cell, then choose Data…
- Compress Pictures in PowerPoint: Step-by-Step GuideTo compress pictures in PowerPoint, select a picture, open Picture Format > Compress Pictures, choose whether to compress…
- Find and Replace in Word: Steps, Options, and TipsIn Microsoft Word for Microsoft 365 on Windows, open Find and Replace with Ctrl+H, enter the text to…
- How to Create and Set Up an Email Signature in OutlookTo create an email signature in Outlook, open the signature settings for your version, make a new signature,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.