Excel 2024: Split Text into Words Using TEXTSPLIT

Many people had been asking for TEXTSPLIT. It was one of the most popular requests on the Excel UserVoice site. I often used a three-line User-Defined Function from Excel MVP Brad Yundt to create a function to split text into words.

The version of TEXTSPLIT that made it to Excel is better than the simple UDF. The row_delimiter argument lets you split text into new rows. In the figure below, TEXTSPLIT is used without a column delimiter and has a row delimiter of ". " in order to put each sentence into a new row.

The ignore_empty argument will help with text that has multiple spaces between words. In the figure below, cells B3, E3, F3, H3, I3:M3 are empty because the text in A1 has multiple spaces between words. While you could solve this with =TEXTSPLIT(TRIM(A1)," "), you can also tell TEXTSPLIT to ignore empty with =TEXTSPLIT(A1," ",,TRUE). While TRIM removes multiple interior spaces, it is not a suitable solution for other delimiters such as a period or comma.

When you use TEXTSPLIT for both rows and columns, the function fills any empty cells with #N/A errors. Use a Pad_With of "" to prevent the #N/A. Formulas shown below calculate the frequency of all words in the text.