Excel Split Cell Based on Character: Methods That Work

How-to basics · Updated November 21, 2026

Yes. In Excel for Microsoft 365, you can split a cell at a character such as a comma using Data > Text to Columns, or use the TEXTSPLIT formula to create separate results automatically. If by “character” you mean a position rather than a separator, such as splitting after the fifth character, use a fixed-width option or a formula based on that position.

What “split based on a character” means

People usually mean one of two different things when they ask how to split an Excel cell based on a character. The first is splitting at a delimiter: a character that marks a boundary between pieces of text. Commas, hyphens, slashes, spaces, semicolons, and line breaks are common delimiters. For example, the comma in Jordan Lee,Sales,Portland separates a name, department, and city.

The second meaning is splitting at a character position, regardless of what the text contains. A product code such as ABCD7392 might need to be divided after its fourth character. These are different tasks: a delimiter-based split looks for a particular character, while a position-based split counts characters. Choosing the right interpretation prevents an awkward formula or an incorrect column layout.

Before splitting, consider whether the original cell should remain intact. Text to Columns replaces the selected source values with the split results, so copy the data to another column or worksheet first if you need to preserve it. A formula leaves the source cell alone and displays its results elsewhere.

Split at a delimiter with Text to Columns

Text to Columns is a practical choice for a one-time cleanup of a list. On Excel for Microsoft 365 for Windows, select the cells to split and choose Data > Text to Columns. In the wizard, select Delimited, then choose Next. Select the delimiter you need, such as Comma or Space. For a character that is not listed, select Other and enter that character in the box. Check the preview, choose Next, and then choose Finish.

For instance, if A2 contains Jordan Lee|Sales|Portland, selecting Other and entering the vertical bar splits the value into three columns. The wizard handles each occurrence of the delimiter, so a value with two vertical bars creates three fields. If your data has a header row, select the data rows you intend to change and take care not to split the header unintentionally.

Text to Columns also offers a Fixed width option. Use that when fields occupy consistent character positions rather than being separated by a character—for example, when the first four characters of every code form a prefix. The preview lets you add or move break lines before finishing.

On Excel for Mac, the command is also available from the Data tab as Text to Columns, though the dialog layout can vary by release. Excel for the web may not present the same desktop wizard in every account or update. If the command is unavailable, use a formula such as TEXTSPLIT where supported, or open the workbook in the desktop app.

Use TEXTSPLIT for a formula-based result

For a repeatable split that updates when the source changes, use TEXTSPLIT. It is available in Microsoft 365 and Excel 2024. With the text in A2 and commas separating the fields, enter this in B2:

With the value Jordan Lee,Sales,Portland in A2, enter =TEXTSPLIT(A2,",") in B2. The formula returns Jordan Lee, Sales, and Portland in B2, C2, and D2.

The results “spill” into neighboring cells automatically. Leave enough empty cells to the right of the formula for all the pieces; otherwise Excel may show #SPILL!. If the source value has a different number of fields on different rows, the formula can spill a different number of columns on each row, so check that those results will not collide with other data.

To split at a hyphen instead, replace the comma inside the formula with a hyphen. The delimiter must match the actual text: a regular hyphen - is not the same character as an en dash – or an em dash —. If a cell contains a line break as its separator, use this formula instead:

With a line break separating items in A2, enter =TEXTSPLIT(A2,CHAR(10)) in B2. It splits at the line-feed character and spills the pieces across columns.

Split at a fixed character position

If there is no delimiter and every value should break after the same number of characters, use the fixed-width option in Text to Columns or formulas. For a formula, the example below splits after the fourth character. It assumes A2 contains ABCD7392 and puts the two results in separate cells:

With ABCD7392 in A2, enter =LEFT(A2,4) in B2 to return ABCD, and =MID(A2,5,LEN(A2)) in C2 to return 7392.

The second formula starts at position five because the first four characters belong in the first piece. Change the number 4 in the first formula and the starting number 5 in the second formula together to move the boundary. For example, splitting after six characters means use 6 for LEFT and 7 for MID. This approach is useful for consistently structured IDs, but it is unreliable when the fields vary in length.

Handle several delimiters and extra spaces

Some lists use more than one separator. TEXTSPLIT can accept an array of delimiters, so either a comma or a semicolon can mark a boundary. With a value such as red,blue;green in A2, enter this in B2:

With red,blue;green in A2, enter =TEXTSPLIT(A2,{",",";"}) in B2. The results are red, blue, and green across three cells.

That formula uses an array constant with comma-separated entries, as written for standard English-language Excel settings. In some regional settings, Excel uses different list separators in formulas. If Excel rejects the formula, check the separators used by your installation rather than changing the delimiter characters in the data.

Splitting does not necessarily remove spaces around each piece. If A2 contains Jordan, Sales, Portland, the pieces after the commas begin with spaces. To remove leading and trailing spaces from each returned item, wrap TEXTSPLIT in TRIM:

With Jordan, Sales, Portland in A2, enter =TRIM(TEXTSPLIT(A2,",")) in B2. The spilled results are Jordan, Sales, and Portland, without the spaces next to the delimiters.

Choose the right method for your data

  • Use Text to Columns for a quick, one-time split when you are comfortable replacing the selected source data with the output.
  • Use TEXTSPLIT when results should update as source cells change, or when you want to keep the original values.
  • Use fixed width or LEFT/MID when the break is determined by a consistent character count, not by a separator.
  • Use Power Query when you regularly import or clean larger datasets. In Excel for Windows, open the data in Power Query and use the split-column options to split by a delimiter or by character positions. The exact available options depend on the selected split method.

For a one-off list, a formula may be more work than the wizard. For recurring imports, a formula or Power Query is usually easier to refresh than repeating manual steps. If your source is an external file that you reload, Power Query can keep the transformation steps together with the query instead of requiring you to repeat the cleanup by hand.

Common problems and how to avoid them

Text to Columns overwrote the next column. The wizard writes split results into adjacent columns. If those cells already contain information, move or copy that information first, or set a different destination in the wizard before finishing.

A formula displays #SPILL!. Check the cells where the split results need to appear. Remove any values, merged cells, or other obstruction in the spill range, then let Excel recalculate. A formula returning several fields cannot occupy cells that already contain unrelated content.

Some results appear to be missing. Consecutive delimiters can create empty fields. For example, East,,North has an empty item between the two commas. Check the source and the formula’s handling of empty values before assuming Excel removed valid text. A trailing delimiter can also leave a final empty field.

Leading zeros disappear. Text to Columns may interpret split values as numbers or dates. This matters for postal codes, account identifiers, and codes such as 00731, where the zeros are part of the value. In the wizard, select the relevant output column in the preview and set its column data format to Text before finishing. For formula results, TEXTSPLIT returns text pieces; avoid converting them to numbers if their formatting matters.

A split happens in the wrong place. Look closely at the real separator. A space may be a nonbreaking space copied from a website, and a visible dash may be a different Unicode character from the hyphen you entered. If the delimiter is inconsistent, clean or standardize the source values before splitting.

Frequently Asked Questions

Can Excel split one cell into rows instead of columns?
Yes. TEXTSPLIT has a row-delimiter argument. If the delimiter should create results vertically, place it in that argument rather than the column-delimiter argument. For example, with semicolon-separated items in A2, enter =TEXTSPLIT(A2,,";") in B2 to spill the items down a column. Ensure the cells below B2 are empty.

Can I split a cell at every space?
Yes. In Text to Columns, choose Delimited and select Space. With TEXTSPLIT, use a space as the delimiter, as in =TEXTSPLIT(A2," "). Be aware that splitting a full name at spaces can produce more than two pieces when someone has a middle name or a multi-word surname.

Does TEXTSPLIT work in older Excel versions?
TEXTSPLIT requires Microsoft 365 or Excel 2024. In older versions, use Text to Columns for a manual split, or build formulas with functions such as LEFT, MID, FIND, and LEN for the particular data pattern. Older versions may not support dynamic spilling.

How do I split at the first comma only?
Text to Columns normally splits at each occurrence of the selected delimiter. If later commas should remain in the second piece, use a formula designed to locate only the first comma, or adjust the source data before using the wizard. The correct approach depends on whether the first field itself can contain a comma.

Will the split results update if I edit the original cell?
Formula results such as TEXTSPLIT recalculate when the source cell changes. Text to Columns creates values in the destination cells, so those results do not automatically re-split when you later edit the original text.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.