To split text stored in one Excel cell into separate columns, use Data > Text to Columns on a normal range, or use formulas in new columns if the data is still in an Excel table. Excel cannot divide one worksheet cell into two independent cells; it can separate the cell’s contents or create extra rows and columns around it. Before you begin, save a copy of the workbook and decide whether you need to split text, a combined value, or the worksheet layout itself.
What You’ll Need
- Excel for Microsoft 365 on Windows, or a recent Excel version. The main Text to Columns wizard is also available in many desktop versions for Mac.
- A clear delimiter or split rule, such as a comma, space, hyphen, or fixed character position.
- Empty destination columns for the split results, or permission to add columns to the table.
- A saved copy of the workbook if the original values, formulas, or table features are important.
Step-by-Step
- Decide what “split” means for your data. If a cell contains a full name, address, code, or other combined text, you can separate its contents into neighboring columns. If you mean dividing one rectangular worksheet cell into two smaller cells, Excel does not have that feature. You can instead add a column or row and adjust the surrounding layout. If the cell is merged with other cells, unmerge it first using Home > Merge & Center > Unmerge Cells; the contents remain in the upper-left cell.
- Inspect a few records and identify the split point. Look for a consistent separator: for example, a comma between city and state, a space between first and last name, or a hyphen in an item code. Check several rows, including the longest or least typical values. A comma followed by a space may be a better delimiter than a comma alone. If the rule varies from row to row, a simple wizard split may not produce reliable columns.
- Protect the source data and make room for the result. Save a copy, or duplicate the source column before changing it. Count how many output columns you need, then add that many blank columns beside the source. In an Excel table, click a header cell and use the table’s insert-column controls, or type a new header in the column next to the table so it expands. Check that those columns do not contain data you need: Text to Columns can overwrite existing cells without providing a convenient way to restore their contents.
- Choose a method that works with your table. For a quick one-time split, the most dependable Text to Columns workflow is to convert the table to a range, split the data, and optionally make a new table afterward. To retain the table and its filter and structured-reference features, add new table columns and use formulas, or use Power Query. Do not assume that a split operation will preserve every table feature automatically.
- Convert the table to a range if you want to use the wizard. Click inside the table, open the contextual Table Design tab, and choose Table Design > Tools > Convert to Range. Confirm the prompt. Your data and much of its formatting remain, but it is no longer an Excel table: table filter controls, automatic table expansion, and structured-reference behavior are no longer provided by the table object. If retaining those features matters, skip this step and use the formula method below.
- Select only the cells you want to split. Click the first data cell in the source column and select the rest of its data cells, leaving the heading out of the selection. For example, if full names are in A2:A20, select A2:A20 rather than the whole worksheet column. Selecting only the populated range reduces the chance of changing unrelated cells and makes it easier to choose the correct destination.
- Open the Text to Columns wizard. On Windows, choose Data > Text to Columns. On Mac, select the source cells and look for Data > Text to Columns; wording or ribbon placement can differ slightly by Excel release. If you are using Excel for the web and cannot find the command, use a formula or open the workbook in desktop Excel. In the first wizard screen, choose Delimited when a character such as a comma or space separates values. Choose Fixed width only when the split positions are consistent across the selected records.
- Set the delimiter and check the preview. For Delimited, select the separator that matches your data. You can choose Tab, Semicolon, Comma, Space, or Other and enter a character. If values contain repeated delimiters, select the option to treat consecutive delimiters as one only when those repeats should count as a single separator. Check the preview: a full name such as “Maya Chen” should appear in two preview columns if you selected Space. If the preview breaks a value in the wrong place, change the delimiter before continuing. For Fixed width, add or move the break lines in the preview to match the positions you want.
- Choose output settings and finish. In the final wizard screen, review the column data formats. Leave General for ordinary text and numbers, or choose Text for values that must remain text, such as product codes with leading zeros. Set Destination to the first empty result cell if the default would overwrite data; for example, enter B2 when splitting the values from A2:A20 into columns B and C. Confirm that enough destination columns are empty, then select Finish. Review the results and compare several rows with the original values.
- Rebuild the table if you converted it. Add clear headers above the new columns, select the complete data range including its headers, and choose Insert > Table or press Ctrl+T on Windows. Confirm that My table has headers is selected when appropriate. On Mac, use Command+T to create a table in many Excel releases; if that shortcut is assigned differently, use Insert > Table. Check that filters, formatting, and any workbook formulas behave as expected after conversion.
- Use formulas when you want to keep the table intact. Add columns with descriptive headers, such as First name and Last name, and enter the appropriate formula in the first data row. Excel tables usually fill a calculated-column formula down the table automatically. With the original full name in A2 and a single space separating first and last names, enter this in the First name column:
=TEXTBEFORE(A2," "). It returns Maya when A2 contains Maya Chen. In the Last name column, use:=TEXTAFTER(A2," "). It returns Chen for the same input. TEXTBEFORE and TEXTAFTER are available in Microsoft 365 and Excel 2024; they are not available in every older Excel version. These examples assume exactly one space separates the two parts, so inspect records with middle names or extra spaces before filling the formulas down. - Use TEXTSPLIT when a value can produce several parts. If A2 contains a comma-separated string such as “Maya,Chen,Seattle,” put this formula in an empty worksheet area outside the table:
=TEXTSPLIT(A2,","). It returns Maya, Chen, and Seattle in neighboring cells on the same row. TEXTSPLIT requires Microsoft 365 or Excel 2024. Its results spill into adjacent cells, so it cannot spill across occupied cells and should not be entered where the table blocks the output. To keep split results in the table, use separate calculated columns or perform the transformation in Power Query. - Consider Power Query for repeated imports. When you regularly receive a new file with the same format, select a cell in the table and choose Data > From Table/Range. In the Power Query Editor, select the source column and choose Transform > Split Column, then select By Delimiter or By Number of Characters as appropriate. Review the preview and choose how to split, such as at each occurrence or at the left-most delimiter. Choose Home > Close & Load to return the transformed data to Excel. The exact dialog labels can vary by update, but the split is repeatable when you refresh the query with new source data.
Common Mistakes
Confusing cell contents with cell structure: Text to Columns separates text values across existing worksheet columns; it does not cut a cell into smaller grid cells. To change the grid, insert columns or rows and redistribute the content.
Overwriting neighboring data: The wizard places results in cells to the right of the source by default. If those cells contain information, choose a different destination or move the information first. A new-looking table column does not guarantee that its existing values are safe to replace.
Choosing a delimiter that appears inside a value: Splitting on every space can separate a multiword city or a name with a middle name into more parts than you intended. Inspect the preview and use a more specific delimiter, a formula designed for the pattern, or Power Query’s split options.
Losing leading zeros or formatting identifiers as numbers: A code such as 00427 may become 427 if Excel interprets it as a number. Set the affected output column to Text in the wizard, or format the destination column as Text before entering a formula-based result. Confirm the displayed and stored values afterward.
Assuming a formula will spill inside a table: Dynamic array formulas such as TEXTSPLIT need clear neighboring cells for their results. Put a spill formula outside the table, or use individual formulas in separate table columns. Also remember that formula results update when the source changes, whereas Text to Columns creates separated values that do not automatically follow later edits to the original text.
Tips
- Keep an untouched copy of the source column until you have checked the split. Delete it only after confirming that every output is complete and accurate.
- Use clear output headings and keep related fields together. For example, put First name and Last name next to each other rather than separating them with unrelated columns.
- If spaces are inconsistent, clean the source first or use a formula that accounts for extra spaces. Do not choose “treat consecutive delimiters as one” unless an empty value between delimiters should be ignored.
- For dates, postal codes, account numbers, and other values with special formatting, test a few examples before applying the method to every row. Excel may interpret some values differently from how they appear in the source.
- For a one-time operation, Text to Columns is often quickest. For a table that changes regularly, formulas or Power Query are easier to maintain because they can recalculate or refresh when the source data changes.
- On Windows, Ctrl+Z can undo many recent worksheet changes, but do not rely on it as a backup for a multi-step transformation. On Mac, use Command+Z. Save a separate copy before a large split.
Troubleshooting
The wizard is disabled or the table will not accept the split: Confirm that you selected cells in a single source column, not the entire table or multiple nonadjacent ranges. If the selection is still part of a table and Text to Columns cannot write the result, use calculated columns or Power Query, or convert the table to a range before using the wizard. Avoid converting the table if other workbook features depend on it.
The split results appear in the wrong columns: Undo immediately with Ctrl+Z on Windows or Command+Z on Mac if possible. Reopen the wizard, set Destination to the first intended output cell, and check that the necessary columns are blank. If you have already made other changes, restore the saved copy or carefully move the results back into place.
Everything stays in one column: Return to the wizard and check the delimiter setting and preview. If the source uses a different character, choose Other and type it. If values are separated by a consistent number of characters rather than a delimiter, choose Fixed width. If the visible separator is part of a more complex pattern, use formulas or Power Query instead.
Some rows have too many or too few pieces: The source probably does not follow one consistent pattern. Search for outliers, such as extra spaces, missing commas, or values that contain the delimiter themselves. Correct the source where possible, split with a more specific rule, or process the exceptions separately. Do not assume that a visually neat preview guarantees every row is correct.
The formula returns an error or an unexpected result: Check that the referenced source cell contains the expected delimiter and that the formula is supported in your Excel version. TEXTBEFORE, TEXTAFTER, and TEXTSPLIT require Microsoft 365 or Excel 2024. If a TEXTSPLIT result shows a spill error, clear the cells where the result needs to appear and move the formula outside the table.
The table features disappeared: If you selected Convert to Range, the worksheet data remains, but the table object is gone. Select the data and headings and create a new table with Insert > Table. If formulas or reports relied on structured table references, check those references after rebuilding and repair them if needed.
Frequently Asked Questions
Can I split one Excel cell into two actual cells?
No. A worksheet cell cannot be divided into smaller independent cells. Insert a row or column to create more grid cells, then move or split the content into them.
Can I split text in an Excel table without converting it to a range?
Yes. Add new columns to the table and use formulas that return one part per column, or use Power Query. This preserves the table structure more reliably than a direct wizard operation.
Does Text to Columns change the original values?
It replaces the source values with the first split part and writes the remaining parts to destination columns. Copy the source first if you need to preserve the combined text.
How do I split names when some people have middle names?
A simple split on spaces can create three or more parts, so decide whether you want the first word and everything after it, or another specific rule. For consistent names, use TEXTBEFORE for the first part and TEXTAFTER for the remainder; review exceptions such as suffixes and compound surnames.
Can I undo a split?
Use Ctrl+Z on Windows or Command+Z on Mac immediately after the operation. If you have continued editing, undo may also reverse those later actions; a saved copy is the safer way to restore original data.
Which method is best for new data arriving every week?
Use Power Query when the source follows a repeatable format, then refresh the query for each new set of data. Use table formulas when you want results calculated alongside each row as values are added.
Related
- Count Excel Rows While Excluding Hidden Rows with SUBTOTAL
- COUNTA in Excel: Count Nonblank Cells for Data Analysis
- Power Query Excel: Aggregate Data into Summary Tables
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel IFS Function Examples for Multiple ConditionsExcel’s IFS function checks conditions in order and returns the value paired with the first condition that is…
- How to Format Proper Names in Excel with PROPER FunctionTo capitalize names in Excel, put this formula in a blank helper column beside the first name: =PROPER(A2).…
- Excel Formula for Years and Months of Service ExplainedIf the hire date is in A2 and the end date is in B2, use =DATEDIF(A2,B2,"y")&" years, "&DATEDIF(A2,B2,"ym")&"…
- Aggregate Excel Columns with Formulas and PivotTablesTo aggregate Excel columns, use a function such as SUM, AVERAGE, COUNT, MIN, or MAX across the cells…
- Excel Sum Negative Numbers: Formulas, Filters, and FixesExcel’s SUM function already includes negative numbers when you add a range: their values reduce the total. To…
- Excel Table Header Formula: What Works and What Doesn’tExcel table headers are column labels, not the place to put a formula you expect to calculate or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.