Split text in excel formula - Excel Tips and Tricks

Discover how to Split text in Excel (formula). We will be providing answer for questions like, is there a way to separate word in a cell in Excel? And also what is the excel formula for split text, and how do you separate word in cell into two cells?

I'll be demonstrating how to split text in Excel for both Microsoft 365 and Microsoft Office suite.

Here are the formulas that will use on the video. I've also included a detailed explanation of what each section of the function do.

Get Word In Microsoft 365
=INDEX(TEXTSPLIT(A2, " "),,5)

Here's a breakdown of the formula:

1) TEXTSPLIT(A2, " "): This function takes a text string in cell A2 and splits it into separate pieces based on the specified delimiter, which in this case is a space (" "). This creates an array of text values.

2) INDEX(array, row_num, column_num): The INDEX function is used to retrieve a specific value from an array. In this formula, the array is the result of the TEXTSPLIT function, and the row_num argument is left empty (,,), which means it will return all rows. The column_num argument is set to 5, indicating that the formula should extract the fifth element from each row of the array.

Get Word In Microsoft Office
=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))), (5-1)*LEN(A2)+1, LEN(A2)))

Here's a breakdown explanation of the Excel formula in Microsoft Office

1) SUBSTITUTE(A1," ",REPT(" ",LEN(A1))) replaces each space in the string with a repeating sequence of spaces that has the same length as the original string. This ensures that each word is separated by a single space.
2) (5-1)*LEN(A1)+1 calculates the starting position of the 5th word by multiplying the length of the string by 4 (since we want the 5th word) and adding 1.
3) MID(..., (5-1)*LEN(A1)+1, LEN(A1)) extracts a substring starting from the calculated position and has the length equal to the length of the original string. This will give you the 5th word along with any leading or trailing spaces.
4) TRIM(...) removes any leading or trailing spaces from the extracted substring, giving you the 5th word only.


Is there a way to separate words in a cell in Excel?,What is the Excel formula for split text?,How do you separate words in a cell into two cells?,
split text in excel formula,excel split words in cell multiple columns,excel split words in cell multiple cells,excel split words in cell formula,excel split text by delimiter formula,excel split text by space formula,how to separate text in excel shortcut,excel split string by length,