To split a cell into two columns in Excel, select the cells, choose Data > Text to Columns, select Delimited, choose the separator in your data, set a destination with two empty columns, and select Finish. This works well when each cell uses the same separator. Make a copy of the original data first, because the results can overwrite cells beside it.
For example, a cell containing Mia Chen can become Mia and Chen in neighboring columns by splitting at the space. If you need to split only at the first separator, or want the result to update automatically when the source changes, formulas may be a better choice. The instructions below are for Excel for Microsoft 365 on Windows; Mac and web notes are included where they matter.
What You’ll Need
- An Excel workbook with the values you want to split in one column.
- A consistent separator, such as a comma, space, semicolon, or pipe character, if you plan to use Text to Columns.
- At least two empty columns beside the source data, or another empty destination area. Keeping the original column is safer than splitting over it.
- For the formula option, Excel for Microsoft 365 or Excel 2024, which includes the TEXTBEFORE and TEXTAFTER functions.
- A saved copy of the workbook if the source data is important or difficult to recreate.
Step-by-Step
- Check how the values are structured. Look at several cells, not just the first one. Identify the character that separates the two pieces: for example, a comma in
Garcia, Elena, a space inMia Chen, or a pipe inNorth|West. Confirm that the same separator is used consistently. If the separator also appears inside a value, a simple split may create extra columns. - Make room for the results. Save a copy of the workbook or duplicate the source column on the sheet. Make sure the two columns where the split results will go are empty for every row you plan to process. Text to Columns writes into cells; it does not insert new columns automatically. If other data sits to the right, insert enough blank worksheet columns first or choose a safe, empty destination.
- Select the source cells. Click the first cell in the source column and drag down to the last cell, or click the column letter if the entire column contains the same kind of data. Avoid selecting unrelated headings or neighboring columns. If you include a heading, Excel will try to split it too; that may be fine, but it is often cleaner to leave the heading out and type separate headings above the results.
- Open the conversion wizard. On the Windows ribbon, select Data > Text to Columns. In the wizard, choose Delimited when a character such as a comma or space marks the split. Choose Fixed width only when the values align at consistent character positions, such as codes where the first four characters always form one field. Select Next.
- Choose the delimiter and check the preview. Tick the matching delimiter option, such as Comma, Space, or Semicolon. For another single character, select Other and type it in the box. Watch the preview at the bottom of the wizard: the vertical dividers show where Excel plans to split. Untick any delimiter that is not part of the intended boundary. If your data uses a comma followed by a space, selecting comma will split at the comma and may leave a leading space in the second result; you can clean that up afterward or use a formula that splits at the exact text.
- Decide how to handle repeated separators and quoted text. The Treat consecutive delimiters as one option is useful when multiple spaces should count as a single separator, such as between names. Leave it off if consecutive separators represent an empty field that you need to preserve. The Text qualifier setting can protect delimiters inside quoted text, depending on how the source is formatted. Check the preview with examples that include repeated separators or quotation marks before continuing.
- Set the destination and data format. Select Next to reach the final screen. Set Destination to the top-left empty cell for the results, such as
B2if the source begins inA2and columns B and C are clear. Choose a column in the preview and set its format if needed. This matters for values such as postal codes or product IDs with leading zeros: leaving them as General may cause Excel to remove those zeros. Select Text for such values. - Finish and inspect both columns. Select Finish, then check the first, middle, and last rows. Confirm that the first piece is in the first result column and the second piece is in the next one. Also check rows with the longest values, blank cells, or unusual punctuation. If the output is correct, add clear headings such as
First nameandLast name. - Use formulas when the split should be repeatable. If the source is in A2 and contains
Mia Chen, enter the following in B2 to returnMia:
With the source value in A2 as Mia Chen, this formula entered in B2 returns Mia:
=TEXTBEFORE(A2," ")
With that same source value in A2, enter this formula in C2 to return Chen:
=TEXTAFTER(A2," ")
These formulas split at the first space. Select B2 and C2 and fill the formulas down for the remaining rows, using the fill handle or copying and pasting. If a value has no space, these formulas return an error; check the source data or use an error-handling formula if missing separators are expected. TEXTBEFORE and TEXTAFTER are available in Microsoft 365 and Excel 2024. Excel for Mac has the same functions in supported versions. If you do not see Text to Columns in Excel for the web, open the workbook in the desktop app or use a supported formula approach.
Common Mistakes
- Splitting into occupied cells: Text to Columns can replace existing contents in its output area. Choose a destination with enough clear columns and rows, and keep the source intact until you have checked the results.
- Choosing the wrong delimiter: A comma is not the same as a comma-and-space sequence. The preview helps reveal whether Excel is splitting at the intended character and whether spaces will remain at the start or end of a result.
- Expecting exactly two results when a separator repeats: Text to Columns splits at each selected delimiter. A value such as
Anna Maria Lopezsplit on spaces produces three pieces, not two. Use formulas that split at the first space if the second column should containMaria Lopez. - Using Space when spaces are part of a field: A space delimiter can divide multiword names, streets, or descriptions into several columns. Decide whether you want every word separated or only the first part separated from the remainder.
- Letting Excel change identifiers into numbers: Codes like
00741can become741if Excel interprets them as numbers. Set the output column to Text in the wizard when leading zeros matter. - Not checking the full selection: One unusual row may have a missing delimiter, an extra delimiter, or a different format. Inspect more than the first result before deleting or replacing the original data.
Tips
- Use a distinctive separator when you control the source. A character such as
|is less likely to appear in ordinary text than a space or comma, so it can make the split more predictable. - Keep a backup until the output is verified. Once the split looks right, you can copy the results and use Paste Special > Values if you want fixed values rather than formulas. Keep the original column until you are sure no row was lost or altered.
- Use formulas for changing data. Text to Columns is a one-time conversion. Formula results recalculate when the source value changes, which is useful for a regularly refreshed list.
- Choose the split rule based on the desired result. If a full name is in A2 and you want the first word in one column and everything after it in another, formulas using TEXTBEFORE and TEXTAFTER split at the first space. If you want every name part separated, Text to Columns with Space is more suitable.
- Set headings and formats deliberately. Give each output column a useful heading, and check dates, numbers, identifiers, and leading zeros before sharing the workbook. A correct-looking split can still contain a changed data type.
- Remember the Mac path. In Excel for Mac, look for Data > Text to Columns on the ribbon; wizard labels and layout can differ slightly by release. Excel for the web has a more limited set of desktop tools, so formulas or opening the workbook in the desktop app may be preferable.
Troubleshooting
The output appears in the wrong columns. Undo immediately with Ctrl+Z on Windows (Command+Z on Mac), then run the wizard again and set the destination to the correct empty cell. If Undo is no longer available, restore the backup or recover the original values before trying again.
The second column has a leading space. This commonly happens when the source looks like Garcia, Elena and you choose comma as the delimiter. The comma is removed, but the following space remains. You can select the affected result cells and remove unwanted spaces using Find and Replace carefully, or split at the exact comma-and-space sequence with a formula-based method. Verify that you are not removing meaningful spaces inside the text.
You get more than two columns. Excel has found the chosen delimiter more than once in a row. If the second result should contain the rest of the text, use the formula option that splits only at the first separator. If you use Text to Columns, review the preview and consider whether a different delimiter or a more specific source format is needed.
Some rows stay together or show an error with formulas. The separator may be missing, different, or represented by more than one space. Search the source column for those rows and standardize the data, or handle exceptions separately. A formula that expects a space cannot split a value that has none.
Leading zeros or dates change. Undo if possible, repeat the wizard, and set the relevant output column to Text before finishing. For values already converted, changing the cell format afterward may not restore missing zeros; use the original source or a backup to recover them.
Text to Columns is unavailable or the screen looks different. Confirm that you are using Excel rather than a preview or another spreadsheet app, and check the Data tab. In Excel for the web, feature availability can differ from the desktop ribbon; use supported formulas or open the file in Excel for Microsoft 365 on Windows or Mac.
Frequently Asked Questions
Can I split one Excel cell into exactly two columns?
Yes. Text to Columns can produce two columns when each selected row has one occurrence of the chosen delimiter. If some rows contain the delimiter more than once, use a first-occurrence formula or review those rows separately.
Will splitting cells delete the original data?
It can overwrite cells in the destination area, but it does not need to replace the source column. Choose an empty destination such as B2 when the original values are in A2:A, and keep a backup until you verify the output.
Can I split names into first and last names?
Yes, if the format is consistent. Splitting on a space works for simple two-part names. Names with middle names, multiple surnames, or different ordering need a more specific rule; a basic space split may create more than two pieces.
Can I split at a comma and remove the following space?
Yes. Text to Columns using comma may leave the space in the second result. You can clean that result after checking it, or use formulas designed to split at the comma and following space. Preview the data before applying a change to the full column.
Do I need a formula to split a cell?
No. Text to Columns is often the quickest option for a one-time split. Formulas are useful when the source may change, when only the first separator should count, or when you want the split to update automatically.
Does this work in Excel for Mac or Excel for the web?
Excel for Mac includes a similar Text to Columns command and supports the same approach in current releases. Excel for the web may not expose every desktop wizard feature; use formulas where supported or open the workbook in the desktop app.
Related
- Text Split in Excel: Polish Menus and Methods
- Text Split in Excel: How to Separate Data into Columns
- Rate Quotation Format in Excel: A Practical Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Sort by Row in Google Sheets: How to Reorder Data SafelyTo sort columns by the values in a row in Google Sheets, select the full range of columns…
- How to Add a Shared Mailbox in Outlook on the Web (OWA)To add a shared mailbox in Outlook on the web, sign in with an account that has permission…
- Excel Keyboard Shortcut to Insert Today’s DateIn Excel for Microsoft 365 on Windows, press Ctrl+; (Control plus the semicolon key) to insert today’s date…
- Excel Text Wildcards: Asterisk, Question Mark, and TildeExcel text wildcards are special characters you can use in criteria to match text without typing the whole…
- Create a Box Plot in Excel: Steps and InterpretationTo create a box plot in Excel for Microsoft 365 on Windows, put each group of numeric observations…
- How to Double Space Lines in Microsoft WordTo double-space text in Microsoft Word, select the text, open Home > Line and Paragraph Spacing, and choose…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.