Function Problems

How to Populate and Shorten Text from Another Excel Sheet Using TEXTAFTER

Maira Mehtab Sep 21, 2026 871 views

Question details

The user needs to extract the final word from text strings (such as dish names) located on a different worksheet and populate those shortened texts across multiple columns and rows.

Product: Spreadsheet
Device & OS: not provided
Scenario: Extracting specific text data from a separate worksheet to organize and shorten entries in a new sheet.
Observed behavior: The user wants to isolate the last word from a longer text string and automate this extraction efficiently across multiple cells without manual data entry.

Before you start

Ensure you have the exact name of the source worksheet and verify that your version of Excel or WPS Spreadsheet supports the TEXTAFTER function.

Solution 1Recommended

Use the TEXTAFTER Function to Extract the Last Word

Use the TEXTAFTER function with a negative instance number to isolate the final word of a text string from another sheet.

The TEXTAFTER function returns text that occurs after a specified character or string. By using a negative instance number (like -1), it searches from the end of the text backwards. This makes it a perfect tool for extracting the last word in a string, such as pulling a generic category from a specific dish name.

Select the target cell on the destination sheet

Open your destination worksheet (e.g., Sheet 2) and click on the cell where you want the shortened text to appear, such as cell G2.

Enter the TEXTAFTER formula

Type the formula =TEXTAFTER('Sheet 1'!G2, " ", -1) into the formula bar. Replace 'Sheet 1' with the actual name of your source worksheet if it is different.

Apply the formula across adjacent cells

Press Enter to apply the formula. Then, click and drag the fill handle (the small square at the bottom-right of the selected cell) across the required columns (e.g., to H2 and I2) and drag it down the rows to populate the rest of your data.

Extraction Result: If the original cell contains 'Parsnip, Maple & Thyme Soup', the formula will successfully return 'Soup'. 'Smoked Salmon' will return 'Salmon'.

Efficient Data Extraction in WPS Office

Easily Manipulate and Extract Text with WPS Spreadsheet

WPS Spreadsheet provides powerful text functions, including advanced formulas for data extraction. You can easily reference other sheets and manipulate text strings just as seamlessly as you would in Microsoft Excel.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data you want to shorten.
2. Input the text extraction formula: Select the target cell on your new sheet and enter your text extraction formula (like TEXTAFTER), referencing the source sheet.
3. Drag to fill the remaining cells: Use the fill handle in the bottom right corner of the cell to quickly drag and copy the formula across adjacent columns and rows.

Fully compatible with Microsoft Excel formulas, functions, and .xlsx file formats.Supports advanced text manipulation functions for quick data cleaning and extraction.Lightweight software with a fast, responsive interface for large datasets.Free and easy-to-use alternative for all your daily spreadsheet tasks.

microsoft office alternative - wps office

Frequently Asked Questions

What if the TEXTAFTER function is not available in my version of Excel?

If TEXTAFTER is not supported in your current software version, you can use a combination of the RIGHT, LEN, SUBSTITUTE, and FIND functions to extract the last word. An alternative formula is: =RIGHT(G2, LEN(G2) - FIND("*", SUBSTITUTE(G2, " ", "*", LEN(G2) - LEN(SUBSTITUTE(G2, " ", ""))))).

How do I extract the first word instead of the last?

To extract the first word, you can use the TEXTBEFORE function by entering =TEXTBEFORE(G2, " "). Alternatively, you can use the LEFT and FIND functions with the formula =LEFT(G2, FIND(" ", G2) - 1).

Why does my formula return a #NAME? error?

A #NAME? error typically occurs if the function name is misspelled or if your spreadsheet software version does not support the TEXTAFTER function. Ensure your software is up to date and check the spelling in the formula bar.

Can I reference a sheet name that contains spaces?

Yes, but if your source sheet name contains spaces, you must enclose the sheet name in single quotation marks within the formula. For example, use 'My Source Sheet'!G2 instead of My Source Sheet!G2.

Excel & Data Problems