String Split Helper
Preview text split by a literal delimiter, with TEXTSPLIT for Microsoft 365 and guidance for older Excel versions.
Inputs
Updates as you typeMaximum 2000 characters · Current 19. Some emoji count as 2 characters.
Maximum 100 characters · Current 1. Some emoji count as 2 characters.
Every part is retained, including empty parts between delimiters. Put the source in A1 and keep the formula output area clear.
Results
Split items
TEXTSPLIT · Microsoft 365
=TEXTSPLIT(A1,"-")Overview
Preview text split by a literal delimiter, with TEXTSPLIT for Microsoft 365 and guidance for older Excel versions. Use it to break line numbers or equipment tags into component columns for sorting or pivot-table summaries.
Formula
Microsoft 365: =TEXTSPLIT(A1,"-") Excel 2016/2019: Data → Text to Columns
Example
How to use
- Enter the source and its literal separator. Splitting the sample on a hyphen creates five items.
- Check empty items from adjacent or trailing separators, spaces and leading zeros such as 033 in the preview.
- For 365, put the source in A1 and the formula in another cell with empty space to the right. For 2016/2019, run Text to Columns on a copy of the source.
Engineering notes
- Delimiters are literal text, not regular expressions. Consecutive delimiters can produce empty items.
- For older Excel's single-character delimiter workflow, replace multi-character separators first. Quotes in generated formulas are escaped for Excel.
- The source limit is 2,000 characters and the delimiter limit is 100. Empty delimiters are rejected. Typing \t or \n does not turn that text into a tab or newline.
- TEXTSPLIT is also available in Excel 2024. Text to Columns can overwrite existing cells on the right, so copy the source and reserve an empty output area.
- TEXTSPLIT returns a dynamic array that spills into the cells to the right. If any of them already hold values you get #SPILL!, so keep the area clear.
Frequently asked questions FAQ
How can I keep the leading zeros in 033?
In Text to Columns, set the relevant column's data format to Text. Converting a tag to a number removes its leading zeros.
Why are there empty items between consecutive separators?
Splitting a--b on - produces a, an empty item and b. This helper and the default TEXTSPLIT formula preserve empty items and column positions. To omit them, review a separate Excel formula with ignore_empty set to TRUE.
How do I get the items in a column instead of a row?
Leave TEXTSPLIT's second argument (column delimiter) empty and put the delimiter in the third (row delimiter): =TEXTSPLIT(A1,,"-") fills the five items downward.
Do I have to split everything to get just the nth item?
No. In Microsoft 365, =INDEX(TEXTSPLIT(A1,"-"),3) returns only the item you want, and the delimiter formulas from the text extract helper give the same result.
Related tools
Excel Text Extract Helper
Suggest fixed-position or delimiter-based Excel formulas from source text and the substring you want to extract.
Open calculatorUnit Conversion Formula Helper
Copy arithmetic or Excel CONVERT formulas that convert the value in cell A1 to another engineering unit.
Open calculatorCommon Excel Formula Examples
Explore common text functions using a fixed pipe-tag sample, with each formula and its expected result.
Open calculator