How to Split Text into Columns with a Formula in Excel

TEXTSPLIT spills delimited text across cells. Split multi-select survey answers, handle mixed separators and count the choices.

You will learn to

• Split delimited text into columns with TEXTSPLIT
• Split on several delimiters at once
• Count the pieces with COUNTA

How it works

=TEXTSPLIT(text, col_delimiter) breaks text apart and spills each piece into its own cell: =TEXTSPLIT("Email;Phone;Live chat",";") fills three cells to the right. Unlike Text to Columns, it is a live formula, so it updates when the source changes. It needs Excel for Microsoft 365 or Excel 2024.

Pass an array constant to split on several delimiters at once — {";",","} handles mixed separators. Because TEXTSPLIT returns an array, you can feed it to other functions: =COUNTA(TEXTSPLIT(B2,";")) counts the items in a list, and INDEX(TEXTSPLIT(...),2) picks the second one.

=TEXTSPLIT(B2,";")