Excel Split Cells With Regex: Formulas and Methods

Formulas & functions · Updated November 21, 2026

Yes—Excel for Microsoft 365 can split text using a regular expression by replacing regex-matched separators with a marker and passing that result to TEXTSPLIT. If the text to split is in A2, use =TEXTSPLIT(REGEXREPLACE(A2,"\s*[,;|]+\s*","~"),"~"). Excel does not have a dedicated REGEXSPLIT function, so this two-function method is a practical workaround.

What regex splitting means in Excel

A normal split uses a specific delimiter, such as a comma or a space. A regular expression, or regex, describes a pattern instead. That pattern might match any one of several separators, a run of repeated separators, or a separator with optional spaces around it. This is useful when a column contains entries formatted inconsistently, such as some values separated by commas and others by semicolons.

In current Microsoft 365 versions, Excel’s regex functions include REGEXTEST, REGEXEXTRACT, and REGEXREPLACE. They let you test, extract, or replace text that matches a pattern. TEXTSPLIT, by contrast, splits text using a literal delimiter. Combining replacement and splitting lets the regex identify the separators while TEXTSPLIT creates the separate results.

Availability can depend on the Excel update channel and rollout, so a function may not appear in every Microsoft 365 installation at the same time. The regex functions are intended for Microsoft 365, including supported Windows, Mac, and web versions; they are not a safe assumption in older perpetual-license editions. TEXTSPLIT is available in Microsoft 365 and Excel 2024. If Excel reports an unknown function, check that the app is updated and that your version includes the function.

Split on several possible delimiters

In the opening formula, the regex pattern \s*[,;|]+\s* means optional whitespace, followed by one or more commas, semicolons, or vertical bars, followed by optional whitespace. The brackets define a group of acceptable single characters; the plus sign allows a run of them. The replacement changes each matching separator run into a tilde, and TEXTSPLIT splits at that tilde.

For example, if A2 contains North, West; 42 | Active, the formula returns four values in adjacent cells: North, West, 42, and Active. The spaces around the separators are part of the regex match, so they do not remain at the beginning or end of each result. The output spills horizontally into the cells to the right; make sure those cells are empty.

For a list separated only by commas or semicolons, remove the vertical bar from the character class. For example, with the same assumption that A2 contains the input string, this version splits on comma or semicolon and ignores surrounding whitespace:

=TEXTSPLIT(REGEXREPLACE(A2,"\s*[,;]+\s*","~"),"~")

Choose a replacement marker that does not occur in the source text. If a tilde could be part of a real value, use another uncommon character or a more controlled approach. Otherwise, TEXTSPLIT would also split at that genuine tilde, which would create an unintended extra field.

Build a pattern for your actual data

The regex should describe the separator you want to remove, not merely characters that happen to appear between values. Small pattern changes can alter the result substantially. These building blocks are useful in Excel’s PCRE2-based regex functions:

  • [,;|] matches one comma, semicolon, or vertical bar.
  • + matches one or more occurrences of the preceding item, so [,;|]+ treats consecutive separators as one break.
  • \s* matches zero or more whitespace characters, including spaces around a delimiter.
  • \d+ matches one or more digits, while \w+ matches a run of word characters.
  • ^ and $ mark the beginning and end of a string, which can help constrain a pattern to the entire cell.

For instance, when values are separated by a slash with inconsistent spacing, a pattern such as \s*/\s* can match the slash and nearby whitespace. If the slash is also allowed inside a legitimate value, however, that pattern may split too much. Regex cannot infer which slash is meaningful; you must define the data rule, such as “split only at the first slash” or “split at slashes surrounded by spaces.”

Excel’s regex functions use PCRE2 syntax, not every feature or convention from other regex tools. A pattern copied from JavaScript, VBA, or an online regex tester may need adjustment. Test it on representative examples, especially strings with punctuation, empty fields, or unusual spacing, before applying it to a whole column.

Keep selected parts with capture groups

Sometimes the goal is not to split every field, but to separate a known pair of parts, such as a surname and a first name. REGEXEXTRACT can return captured groups directly. Parentheses identify the pieces to return, while the surrounding pattern specifies how they are arranged.

Assume A2 contains Chen, Mina, with a surname, comma, and first name. This formula returns the two captured parts in separate adjacent cells:

=REGEXEXTRACT(A2,"^([^,]+),\s*(.+)$",2)

The first group, ([^,]+), captures one or more characters that are not commas. The second group, (.+), captures the remaining characters after optional whitespace. The final argument, 2, tells REGEXEXTRACT to return capture groups. This approach is more precise than replacing separators when the number and order of fields are known and you only want particular pieces.

Capture-based extraction also helps when separators are not identical but the field structure is stable. For example, a pattern can capture a product code and a description around a known marker. Make the pattern specific enough to reject malformed rows rather than quietly returning misleading pieces. If some records do not follow the assumed structure, consider testing them with REGEXTEST or handling exceptions separately.

Handle empty fields, repeated separators, and marker conflicts

Repeated separators require a deliberate choice. The plus sign in [,;|]+ treats adjacent punctuation as a single separator. That is often right for messy imported text, such as red,,blue, where the extra comma is accidental. But if two delimiters are meant to represent an empty field, collapsing them loses that information. In that case, match one delimiter at a time and decide how the empty field should be represented.

There is another subtlety: trimming whitespace as part of the matched separator is convenient, but it can remove spaces that are meaningful at a field boundary. Similarly, a separator character may be part of quoted text, such as a comma inside "12 Main St, Unit 4". A simple regex split does not automatically understand CSV quoting rules. If your source follows a structured format with quotes and escaped quotes, use a CSV-aware import process rather than splitting blindly on every comma.

Before filling a formula down, check a few cases: a normal row, a row with multiple delimiter types, one with consecutive separators, one with a blank value, and one containing the marker you chose. A formula that works for the cleanest row may still break on a realistic exception. For a repeatable workflow, document the delimiter rule and retain the original source column so that results can be checked or regenerated.

Use the results in a worksheet

Because TEXTSPLIT returns a dynamic array, one formula can produce several cells without copying a separate formula into each output column. In a table or regular range, enter the formula beside the source data and fill it down as appropriate. Each row’s results need clear space to spill. A nonblank cell in the output area can cause a #SPILL! error; clear the obstruction or move the formula.

If you need the results vertically instead of horizontally, TEXTSPLIT supports a row delimiter as well as a column delimiter. With the same assumption that A2 contains the string and the regex replacement produces tilde-separated pieces, the second argument sets the column delimiter and the third sets the row delimiter:

=TEXTSPLIT(REGEXREPLACE(A2,"\s*[,;|]+\s*","~"),,"~")

The empty second argument leaves the column delimiter unspecified, while the tilde is used as the row delimiter. This spills the pieces downward. Since the empty argument is easy to overlook, use this form only when vertical results are useful and test it in your Excel version.

For a large batch of source records, formulas keep the transformation visible and update when the source values change. If the data is a one-time cleanup, you can copy the results and use Paste Special > Values to keep only the separated values. Preserve a copy of the original data before replacing anything; splitting can change the layout and is not always reversible without the original strings.

Regex formulas versus other Excel methods

Regex is most useful when separators vary or follow a pattern. It is not automatically the best method for every split:

  • Use TEXTSPLIT alone when every row uses the same exact delimiter. It is simpler and avoids introducing a marker.
  • Use REGEXREPLACE with TEXTSPLIT when several delimiter characters or variable separator runs should count as one boundary.
  • Use REGEXEXTRACT when the desired fields have a known structure and capture groups can identify exactly what to keep.
  • Use Text to Columns for a straightforward, one-time split by a fixed delimiter. On Windows, find it on the Data tab in the Data Tools group. It is not a regex parser and does not continuously recalculate from the source.
  • Use Power Query for repeatable import and cleanup steps, especially when data is refreshed regularly. Its standard split-by-delimiter options do not provide general regex splitting, so regex-specific processing may need another approach.

These options solve different problems. A formula is convenient when the output should refresh automatically alongside worksheet data. Text to Columns is a quick interactive operation, while Power Query is better suited to a documented import-and-transform process. Neither should be described as regex splitting unless a regex function is actually used.

Common errors and compatibility checks

If Excel shows #NAME?, it may not recognize one of the functions. Confirm that the workbook is open in a Microsoft 365 version with the regex functions available and that the function names are spelled correctly. Some organizations control update timing, so two Microsoft 365 users may not receive a new function on the same date. Excel for the web and Mac may differ in rollout timing or interface details, but the formula idea is the same when the functions are supported.

A #SPILL! result usually means the destination cells are not empty or the formula is inside a layout that cannot accommodate the array. Clear the cells where the output needs to appear or place the formula in an open area. If a result looks joined or mis-segmented, inspect the regex pattern and the source string; the problem is often an unintended match, a marker collision, or a separator that the pattern does not include.

Excel’s regional settings can affect the argument separator used in formulas. The examples here use commas, as in the standard syntax requested for Microsoft 365 on Windows. A configured installation may display semicolons as argument separators; if so, use the separator Excel expects in that installation. This is different from the commas and semicolons inside the quoted regex pattern, which are ordinary text characters and should remain as written.

Frequently Asked Questions

Does Excel have a REGEXSPLIT function?
No. Excel provides regex functions for testing, extracting, and replacing text, but not a dedicated REGEXSPLIT function. A common workaround is to replace regex-matched separators with a safe marker and split on that marker with TEXTSPLIT.

Can I split a cell on commas or semicolons at the same time?
Yes. Put the allowed characters in a character class such as [,;], then replace matches and split on the replacement marker. Add whitespace matching if spaces around separators should be removed.

Why does my formula return #NAME?
The Excel build may not include the regex function yet, or the function name may be misspelled. Check for Microsoft 365 updates and confirm the function is available in your installation. Older Excel editions do not necessarily support the current regex functions.

Can regex split quoted CSV data correctly?
A simple separator pattern cannot reliably treat commas inside quoted fields as ordinary text while splitting only commas between fields. For properly quoted CSV, use a CSV-aware import workflow such as Excel’s data import tools or Power Query.

Can I split the results into rows instead of columns?
Yes. TEXTSPLIT accepts a row delimiter as its third argument. Use the replacement marker as that row delimiter and leave the column-delimiter argument empty, as shown above.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.