To split a cell in Excel, select the column, open Data and then Text to Columns, choose Delimited, pick the character that separates the values such as a comma or a space, and finish. Excel splits each cell into its own columns. That wizard is the fastest method and works in every version of Excel. If you want the split to update automatically when the source changes, use the TEXTSPLIT function instead, and if you just want Excel to copy a pattern you type, use Flash Fill with Ctrl+E.
This comes up constantly after a PDF conversion, when a date and an amount, or a full name, end up jammed into one cell. Here are all three methods, when to reach for each, and the two settings that stop Excel from mangling the result.
How do you split a cell in Excel?
Select the cells you want to split, go to the Data tab, and click Text to Columns. In the wizard, choose Delimited if a character separates the values or Fixed width if the values line up in even columns, then click Next. Tick the delimiter that sits between your values, comma, tab, space, semicolon, or a custom character, and Excel shows a live preview of the split. Click Finish and each piece lands in its own column to the right. The whole thing takes about ten seconds once you know where it is.
How do you use Text to Columns in Excel?
Text to Columns is a three step wizard. Step one asks Delimited or Fixed width. Delimited is for values with a separator, like "Smith, John" or "01/15/2026 $420.00". Fixed width is for data where each field starts at the same character position, common in old exports. Step two is where you pick the delimiter and preview the columns. Step three lets you set the data format of each new column, and this is the step most people skip.
One warning: the split writes into the columns immediately to the right of your selection and overwrites whatever is there. Insert a couple of blank columns first, or set the Destination box in step three to an empty cell, so you do not wipe out the next column of data.
How do you split a cell using a formula?
Use TEXTSPLIT, available in Excel 365 and Excel for the web. Unlike Text to Columns, a formula recalculates whenever the source cell changes, so it is the right choice for a sheet that updates. To split A2 on commas, enter =TEXTSPLIT(A2, ",") and the results spill across the row. You can split on more than one character, for example =TEXTSPLIT(A2, " ") for spaces. If you are on Excel 2021 or earlier, TEXTSPLIT does not exist and you fall back to Text to Columns or the older LEFT, RIGHT, MID, and FIND combinations.
How do you split text with Flash Fill?
Flash Fill watches the pattern you type and fills the rest of the column to match. Put your data in column A, type the first result you want in column B, then press Ctrl+E. Excel guesses the rule from your example and completes the column. It is excellent for one off jobs like pulling first names out of "John Smith" or extracting a zip code from an address. Flash Fill is available in Excel 2013 and later. It is fast but it is a guess, so scan the output, because an inconsistent source can throw it off partway down.
Which method should you use?
The three methods trade speed for flexibility. This table sorts out which fits your task.
| Method | Updates automatically | Excel version | Best for |
|---|---|---|---|
| Text to Columns | No, one time split | All versions | A quick, static split of a whole column |
| TEXTSPLIT function | Yes, recalculates | 365 and web only | Sheets where the source keeps changing |
| Flash Fill (Ctrl+E) | No | 2013 and later | Pattern based pulls like names or codes |
How do you keep leading zeros when splitting a cell?
In the Text to Columns wizard, set the column data format to Text in step three before you click Finish. By default Excel treats a split value as a number, which strips a leading zero from a zip code or an account number and turns a long ID into scientific notation. Highlighting that column in the wizard and choosing Text keeps the value exactly as written. If you already split the data and lost the zeros, undo, re-run the wizard, and set the format the second time, because reformatting the cell afterward will not bring the missing digits back.
How do you split one cell into multiple rows?
Text to Columns only splits sideways into columns, not down into rows. To turn one cell into several rows, use TEXTSPLIT with a row delimiter, for example =TEXTSPLIT(A2, , ","), where the third argument is the row separator, so the values spill down instead of across. On older versions, Power Query is the reliable route: load the column, use Split Column and choose the option to split into rows. This is common when a single PDF cell holds a stacked list that needs to become individual line items.
Why did my data land in one column after a PDF conversion?
When a converted PDF drops everything into column A, it usually means the tool read the page as lines of text rather than a real table, so every row is one long string. Text to Columns can rescue it if the values are separated by a consistent character, but if the spacing is uneven the split will be messy. The cleaner fix is upstream: use a table aware converter that keeps each field in its own cell from the start. We cover the causes and fixes in detail in why a PDF converts to Excel all in one column, and the broader tidy up in how to clean up data after a PDF conversion. If the messy column came from pasting a bank statement, it is faster to skip the cleanup entirely and use a tool made for turning bank statements into clean rows.
The short version
Text to Columns for a fast one time split, TEXTSPLIT when the sheet needs to stay live, Flash Fill for pattern pulls. Set the column format to Text before you split anything with leading zeros. And if you are splitting data because a PDF to Excel conversion collapsed it into one column, fix the conversion first and you will have far less splitting to do. Keeping the structure intact through the conversion is also the point of a conversion that preserves formatting.