EXCEL / String Split Helper

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 type

Maximum 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

5
/PPD2CPIPEBP033

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

Splitting /P-PD2C-PIPE-BP-033 on a hyphen gives five items: /P, PD2C, PIPE, BP and 033. Delimiters can be longer than one character. If A1 is PUMP / P-101A / 22kW, =TEXTSPLIT(A1," / ") gives three cells: PUMP, P-101A and 22kW. In older versions, first turn it into a one-character delimiter with =SUBSTITUTE(A1," / ","|") and split on | with Text to Columns.

How to use

  1. Enter the source and its literal separator. Splitting the sample on a hyphen creates five items.
  2. Check empty items from adjacent or trailing separators, spaces and leading zeros such as 033 in the preview.
  3. 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.

KEEP WORKING