How to use TEXTBEFORE, TEXTSPLIT, and TEXTAFTER functions in Excel?

When you import the data from another resource, a lot of mistakes are encountered in the dataset which makes it very challenging for the users to manipulate the text therein. In this article, users will learn the concept of TEXTBEFORE, TEXTSPLIT, and TEXTAFTER functions which are exclusive only to Office 365.

The TEXTBEFORE function retrieves the text before the delimiter whereas the TEXTSPLIT function partitions the text with the help of the row and column’s delimiter. The last TEXTAFTER function fetches the text after the delimiter. All these functions are user-friendly and simple to use.

TEXTBEFORE Function in Excel

Step 1

Open the new worksheet and write the sample data as given below −

Step 2

Users must write the formula =TEXTBEFORE(B2, " ") in the C2 cell and then press the “Enter” tab to obtain the text just before the single space. Here, we use the single space delimiters.

Step 3

Place the cursor on the right corner of the C2 cell drag the + sign and drop it to the C8 cell to retrieve the result of the remaining cell.

TEXTSPLIT function in EXCEL

Step 1

Users may use the same dataset as specified in the previous example and write the formula =TEXTSPLIT(B2, " ") in the C2 cell as given below −

Step 2

Press the “Enter” tab to obtain the separated text value. The single space delimiter is used inside the TEXTSPLIT function which means text is partitioned after the single space.

Step 3

Put the cursor on the right bottom side of the C2 cell, expand the + sign, and drop it to the C8 cell to retrieve the result of the remaining cell.

TEXTAFTER function in Excel

Step 1

You may utilize the same dataset and write the formula =TEXTAFTER(B2, " ") in the C2 cell as highlighted below image −

Step 2

Then press the “Enter” tab to retrieve the manipulated result.

Step 3

Drag the + sign presented on the right bottom corner of the C2 cell and drop it into the C8 cell to obtain the results.

Conclusion

A detailed explanation of TEXTAFTER, TEXTSPLIT, and TEXTAFTER functions is given in this article. Users would master Excel proficiency by utilizing these effective techniques and improving their productivity.