TEXTSPLIT function in Excel: split text strings or cells using a specified delimiter
This guide explains how to use the new TEXTSPLIT function in Excel 365 to divide text into segments based on any chosen symbol or space.
In earlier versions of Excel, users relied on features like Text to Columns or Flash Fill to separate text. However, Excel 365 introduces a simpler method—the TEXTSPLIT function. This powerful tool allows you to split text into multiple columns or rows according to a specified delimiter, such as a space, comma, or dash.
Table of Contents
Excel TEXTSPLIT function
The TEXTSPLIT function in Excel helps you split text into parts using a symbol or space you choose. It can put the split parts into columns, rows, or both. The result shows up automatically in many cells.
This function has six settings, but usually, you only need the first two to use it.
- text (Required) – The text you want to split. You can type it directly or use a cell reference.
- col_delimiter (Required) – The symbol that tells Excel where to split the text into columns. If you leave this out, you must use the row_delimiter instead.
- row_delimiter (Optional) – The symbol that tells Excel where to split the text into rows.
- ignore_empty (Optional) – What to do with empty parts:
- FALSE (default) – Keeps empty cells if there are two or more separators next to each other.
- TRUE – Skips empty parts, so no empty cells appear.
- match_mode (Optional) – Whether the split symbol cares about uppercase or lowercase:
- 0 (default) – Case-sensitive (treats uppercase and lowercase as different).
- 1 – Case-insensitive (treats uppercase and lowercase as the same).
- pad_with (Optional) – A value to fill empty spaces if splitting into both columns and rows. If not set, missing parts show an error (#N/A).
For example, to divide a text string in A2 into multiple cells using a comma and a space as the separator, the formula is:
TEXTSPLIT availability
The TEXTSPLIT function is exclusive to Excel for Microsoft 365 on Windows and Mac, as well as Excel for the web.
Basic TEXTSPLIT formula to split a cell in Excel
Let’s begin with the basics of using the TEXTSPLIT function to divide a text string based on a specific delimiter
Split a cell horizontally across columns
To split the text in a cell into different columns, you first tell Excel which cell has the text, then give the character (called a delimiter) that shows where to split.
For example, to split the text in cell A2 at each comma, use this formula:
=TEXTSPLIT(A2, “,”)
In this case, the delimiter is a comma written inside double quotes (“,”). This tells Excel to put each part of the text (separated by commas) into its own column.
Remember: You only need to type the formula in one cell (like C2). Excel will automatically fill the nearby cells with the results.
This group of filled cells is called a spill range. Excel shows a blue border around it to let you know that all the values come from the formula in the top-left cell.
Split text by substring
Sometimes, the text is separated by more than one character — like a comma followed by a space (, ). To split it correctly, you should use the full string as the delimiter.
For example, to split the text in cell A2 using a comma and a space, use this formula:
=TEXTSPLIT(A2, “, “)
Type this formula in B2, then copy it down if you want to use it on other rows.
Split string into columns and rows at once
To split text into both rows and columns at the same time, you need to give two delimiters in the TEXTSPLIT formula.
For example, if the text in cell A2 uses:
- An equal sign (=) to separate columns
- A comma and a space (, ) to separate rows
Then use this formula:
=TEXTSPLIT(A2, “=”, “, “)
This will split the text into a 2D grid — in this case, 2 columns and 3 rows.
Separate cells by multiple delimiters
If your text uses different or messy separators, you can use an array constant ({…}) in the TEXTSPLIT formula. This lets Excel split the text using multiple delimiters at once.
For example, if the text in A2 is split using commas (,) and semicolons (;), with or without spaces, you can use this formula to split it into rows:
=TEXTSPLIT(A2, , {“,”,”, “,”;”,”; “})
If you want to split by commas and semicolons but also remove extra spaces, use the TRIM function:
=TRIM(TEXTSPLIT(A2, , {“,”,”;”}))
This helps keep your data neat by getting rid of unwanted spaces.
Split text ignoring empty values
If your text has two or more separators in a row (like , ,), you can choose whether to keep or skip the empty spaces between them. This is controlled by the ignore_empty setting in the TEXTSPLIT function.
By default, ignore_empty is set to FALSE, which means Excel will keep empty values. This is helpful when your data is in a fixed structure and you want to show all values, even if some are missing.
For example, if cell A2 has some missing scores, use either of these formulas:
=TEXTSPLIT(A2, “, “)
Or
=TEXTSPLIT(A2, “, “, FALSE)
This way, all the values, including the empty ones, will appear in the correct columns.
If your text contains similar types of data (like a list of items), you might want to skip empty values. To do this, set the ignore_empty argument to TRUE or 1.
For example, if you want to split the text below and place each skill in a separate cell without empty gaps, use this formula:
=TEXTSPLIT(A2, “, “, ,TRUE)
In this case, the missing values between consecutive delimiters are ignored completely:
Cell splitting case-sensitive or case-insensitive
You can choose if TEXTSPLIT treats uppercase and lowercase letters as the same by using the match_mode option (the fifth one in the formula).
By default, match_mode is 0, which means TEXTSPLIT is case-sensitive. It only splits where the delimiter matches exactly.
For example, if your numbers are separated by both lowercase “x” and uppercase “X”, this formula will only split at the lowercase “x”:
=TEXTSPLIT(A2, ” x “)
Important: The delimiter ” x ” includes spaces on both sides. This helps remove extra spaces from the results.
To turn off case sensitivity, set the match_mode argument to 1. This makes TEXTSPLIT ignore uppercase and lowercase differences when splitting.
=TEXTSPLIT(A2, ” x “, , ,1)
Now, all the strings are split correctly by either delimiter:
Pad missing values in 2D array
The last option in the TEXTSPLIT function is called pad_with. It helps when some parts of your text are missing.
When you split text into both columns and rows, Excel usually shows #N/A errors where values are missing. This keeps the table’s shape correct.
For example, if your text doesn’t have an equal sign (=) after the word “Score” (which is the column delimiter), TEXTSPLIT will put #N/A in that empty spot. This way, all the data stays lined up properly.
To make the results easier to read, you can replace the #N/A error with any value you want. Just put that value in the pad_with argument.
For example, to replace missing values with a hyphen (“-“), use this:
=TEXTSPLIT(A2, “=”, “, “, , ,”-“)
Or an empty string (“”):
=TEXTSPLIT(A2, “=”, “, “, , ,””)
Now that you’ve learned practical uses of each argument of the TEXTSPLIT function, let’s discuss a couple of advanced examples that can help you cope with non-trivial challenges in your Excel spreadsheets.
Split dates into day, month and year
To split a date into parts like month, day, and year, you first need to change the date into text. This is because TEXTSPLIT only works with text, but Excel stores dates as numbers.
The easiest way to do this is by using the TEXT function. Just use the right format for your date.
For example, to turn the date in A2 into text, use this formula:
=TEXT(A2, “m/d/yyyy”)
Next, use this inside the TEXTSPLIT function as the first argument. Then, choose the slash / as the delimiter since dates are separated by slashes.
The full formula looks like this:
=TEXTSPLIT(TEXT(A2, “m/d/yyyy”), “/”)
This will split the date into three parts: month, day, and year.
Split strings skipping certain values
Supposing you want to separate the below strings into 4 columns: First name, Last name, Score, and Result. The problem is that some strings contain the title “Mr.” or “Ms.”, because of which the results are all wrong:
TEXTSPLIT alternatives
In older Excel versions that do not support TEXTSPLIT, you can still split text by combining functions like SEARCH or FIND with LEFT, RIGHT, and MID. Here’s how:
- SEARCH (case-insensitive) or FIND (case-sensitive) finds where the delimiter is in the text.
- LEFT, RIGHT, and MID then extract the part of the text before, after, or between the delimiters.
For example, if the values in cell A2 are separated by a comma and a space, you can use these formulas:
- To extract the name:
=LEFT(A2, SEARCH(“,”, A2, 1) – 1)
- To pull the score:
=MID(A2, SEARCH(“,”, A2) + 2, SEARCH(“,”, A2, SEARCH(“,”, A2) + 1) – SEARCH(“,”, A2) – 2)
- To get the result:
=RIGHT(A2, LEN(A2) – SEARCH(“,”, A2, SEARCH(“,”, A2) + 1) – 1)
For a detailed explanation of these formulas, see the guide “How to split strings by character or mask.”
Remember, unlike the dynamic array TEXTSPLIT function, these formulas must be entered separately in each cell. You put the formula in the first cell and then drag it down to apply it to the cells below.
The screenshot below shows the results.
And that’s how you can split cells in Excel 365 using TEXTSPLIT or other methods for older versions!
Thank you for reading! 😊 I hope this guide was helpful, and I look forward to seeing you on our blog next week! 🚀
