Exports love joined fields: “Mumbai-MH-400001”, “INV/2026/0145”, “Pens, Files, Staplers”. Text to Columns splits once, but a formula keeps working when new rows arrive.

Excel 365: TEXTSPLIT

=TEXTSPLIT(A2, "-")                 ' → Mumbai | MH | 400001 across columns
=TEXTSPLIT(A2, , ", ")              ' → one item per row (row delimiter)
=TEXTSPLIT(A2, {"-","/"," "})       ' several delimiters at once
=TEXTSPLIT(A2, ",", , TRUE)         ' ignore empty pieces from ",,"

Just one piece: TEXTBEFORE and TEXTAFTER

=TEXTBEFORE(A2, "-")                    ' Mumbai
=TEXTAFTER(A2, "-", -1)                 ' 400001 (after the LAST dash)
=TEXTBEFORE(TEXTAFTER(A2, "-"), "-")    ' MH (the middle)
=TEXTAFTER("INV/2026/0145", "/", 2)     ' 0145 (after the 2nd slash)

Give a fallback for rows without the delimiter instead of #N/A: =TEXTBEFORE(A2, "-", , , , A2).

Get the Nth piece

=INDEX(TEXTSPLIT(A2, "-"), 2)                                       ' Excel 365
=TRIM(MID(SUBSTITUTE(A2, "-", REPT(" ", 100)), (2-1)*100+1, 100))    ' any Excel

The older trick swaps each dash for 100 spaces so every piece sits in its own 100-character slot; MID grabs slot N and TRIM removes the padding. Change the 2 for other pieces.

Numbers come out as text

Split results are text, so “400001” won’t sum or match a number. Wrap with -- or VALUE when every piece is numeric: =--TEXTSPLIT(A2, "-"). Keep codes such as PIN codes or product codes as text.

Split a whole column at once

TEXTSPLIT on A2:A200 returns only the first piece of each row, because arrays of arrays aren’t allowed. Fill the formula down instead, or use:

=DROP(REDUCE("", A2:A200, LAMBDA(acc, x, VSTACK(acc, TEXTSPLIT(x, "-")))), 1)

Where people go wrong

Practice

Download the combo practice workbook below. Its Sales sheet of 200 orders (dates, regions, products, customers, amounts) is ready data to try every formula on this page, and the Practice sheet has 50 checked tasks on related combos.

More: all formula combos · Excel function course.