Excel 2024: Split Text into Words Using TEXTSPLIT
October 18, 2024 - by Bill Jelen

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.

This article is an excerpt from MrExcel 2024 Igniting Excel
Title photo by Jessica Lewis 🦋 thepaintedsquare on Unsplash