How to separate data in excel using formula

Use the TEXTSPLIT function in Excel 365 to split text into rows or columns using delimiters.

This guide teaches you how to use the TEXTSPLIT function, starting with simple text splits and moving on to more advanced formulas.

Basic TEXTSPLIT function

When TEXTSPLIT has only 2 arguments, it splits text into columns.

In this example, we're using a comma and a space as the delimiter (second argument).

Note: the TEXTSPLIT function, entered into cell B2, fills multiple cells (B2 and C2). Wow! This behavior is called spilling.

Split Text into Columns or Rows

The TEXTSPLIT function below, with 2 arguments, splits text into columns using a dash as the delimiter.

The results spill over into the cells to the right of cell B1.

Use 3 arguments and leave the second argument empty to split text into rows!

Multiple Delimiters

The TEXTSPLIT function below splits cell A1 using multiple delimiters (semicolon, dash, and slash).

Explanation: you can split text using various delimiters by providing them as an array constant to TEXTSPLIT.

Ignore Em

how to split word in excel formula

how to separate text in excel