Function Problems

How to Split Text in Excel Using TEXTBEFORE and TEXTAFTER

WPS Editor Oct 9, 2026 869 views

Question details

The user wants to extract specific text from a string into a new column while simultaneously removing that extracted portion from the original cell.

How to Split Text in Excel Using TEXTBEFORE and TEXTAFTER

Product: Excel
Device & OS: not provided
Scenario: Extracting partial text from a string and modifying the original value accordingly.
Observed behavior: Excel formulas cannot modify the contents of another cell directly, so splitting text and altering the source cell requires separate functions and helper columns.

Before you start

Ensure you are using a version of Excel that supports TEXTBEFORE and TEXTAFTER (such as Microsoft 365 or Excel for the Web), as these functions are not available in older standalone versions like Excel 2019.

Solution 1Recommended

Extract and Split Text Using Helper Columns

Since formulas cannot alter their source cells, you must use helper columns with extraction and replacement functions to display the split text separately.

Excel formulas only return values to the cells where they are entered; they cannot delete or change text in the original cell. To achieve a 'split and remove' effect, you will need two new columns: one for the extracted text and one for the remaining text.

Extract text using TEXTBEFORE or TEXTAFTER

In a new column, enter =TEXTBEFORE(A1, "delimiter") or =TEXTAFTER(A1, "delimiter") to pull out the specific portion of the text you need. Replace 'delimiter' with the actual character you want to split by, such as a space or comma. Press Enter.

Return the remaining text using SUBSTITUTE

In another new column, use the SUBSTITUTE function to replace the extracted part with an empty string. Enter =SUBSTITUTE(A1, B1, "") (assuming A1 is your original text and B1 holds your newly extracted text).

Replace the original data (Optional)

If you want to permanently remove the old data and keep only the split results, highlight your two new formula columns, copy them, right-click the original column, and select 'Paste as Values'. You can then safely delete the helper columns.

Extract and Split Text Using Helper Columns

Formula Limitation: Remember that no Excel formula can directly change a value in another cell. You must always use a separate cell to calculate and display the new result.

Efficient Text Processing

Easily Split Text Strings in WPS Spreadsheet

WPS Spreadsheet provides powerful text functions and built-in tools like 'Text to Columns' and 'Smart Fill' to help you extract, split, and manipulate data quickly without relying solely on complex formulas.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office, open your spreadsheet file, and select the column containing the text you want to split.
2. Use the Text to Columns feature: Navigate to the 'Data' tab on the top ribbon and click on 'Text to Columns'.
3. Set your delimiters: Choose 'Delimited' in the wizard, select the specific character (like a space or comma) separating your text, and click 'Finish' to instantly split the text into separate columns.
4. Use Smart Fill for pattern extraction: Alternatively, type the desired extracted text in the adjacent column, press 'Ctrl + E', and WPS Spreadsheet will automatically fill down the rest of the column based on your pattern.

100% compatible with Microsoft Excel formulas and file formats (.xlsx).Built-in 'Text to Columns' tool for instant, formula-free data splitting.Smart Fill automatically extracts text patterns without requiring manual setup.Free, lightweight, and features a familiar tabbed user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why can't the TEXTBEFORE function just delete the text from the original cell?

Excel formulas are designed strictly to evaluate and return a result in the cell they reside in. They do not have the capability to modify, delete, or overwrite data in other cells, which is why helper columns are required.

What should I do if TEXTBEFORE or TEXTAFTER returns a #NAME? error?

This error usually occurs if you are using an older version of Excel that does not support these newer functions. In older versions, you must use a combination of LEFT, RIGHT, FIND, or SEARCH functions to extract text.

Can I split text by multiple different delimiters at once?

Yes, both TEXTBEFORE and TEXTAFTER allow you to use an array of delimiters. For example, entering =TEXTBEFORE(A1, {",", ".", "-"}) will successfully extract the text appearing before any of those three specific characters.

Excel & Data Problems