Function Problems

How to Extract Percentages and Materials from Text in Excel

Maira Mehtab Sep 22, 2026 868 views

Question details

The user needs to isolate specific percentage values and material names from variable-length text strings in product descriptions.

Product: Excel
Device & OS: not provided
Scenario: Processing raw product description data to cleanly separate out the material composition for inventory or analysis.
Observed behavior: The goal is to automatically extract values such as '68%', 'POLYESTER', '32%', and 'POLYURETHANE' that appear after a specific keyword like 'MAIN MATERIAL'.

Before you start

Verify that your version of Excel supports dynamic array functions (such as Excel 365 or Excel for the Web), as these provide the most straightforward method for advanced text splitting.

Solution 1Recommended

Use TEXTAFTER and TEXTSPLIT Functions

Leverage modern Excel text functions to isolate the text following your target keyword and split it into separate cells automatically.

This method uses TEXTAFTER to remove all irrelevant description text before the materials list, and TEXTSPLIT to divide the remaining string into separate columns based on a delimiter like a comma.

Locate your data

Identify the cell containing your product description. For this example, assume the text is located in cell A2.

Isolate the relevant text

Use the TEXTAFTER function to strip away everything before your keyword. Type =TEXTAFTER(A2, "MAIN MATERIAL") into an empty cell.

Split the text into columns

Wrap the previous formula in TEXTSPLIT to separate the percentages and materials. Update your formula to: =TEXTSPLIT(TRIM(TEXTAFTER(A2, "MAIN MATERIAL")), ",")

Apply and review

Press Enter. The formula will spill the results across adjacent columns, cleanly separating items like '68%', 'POLYESTER', '32%', and 'POLYURETHANE'.

Handling Multiple Delimiters: If your source data uses multiple separators (e.g., both commas and spaces), you can pass an array of delimiters into the TEXTSPLIT function like this: {",", "-"}.

Solution 2

Use Flash Fill for Consistent Patterns

If your text data follows a strict, highly consistent structural pattern, you can use Excel's Flash Fill feature to extract data without complex formulas.

Process Text Easily

Extract and Manage Text Data with WPS Spreadsheet

WPS Spreadsheet offers powerful data processing tools and comprehensive formula support, making it simple to extract complex text strings and analyze product materials efficiently.

1. Open your dataset: Launch WPS Spreadsheet and open the document containing your product descriptions.
2. Extract the data: Use supported text extraction formulas like MID, FIND, or advanced splitting tools to isolate your material data.
3. Save seamlessly: Clean the extracted percentages and save your spreadsheet flawlessly in the standard .xlsx format.

Fully compatible with Microsoft Excel formulas and file formats (.xlsx)Supports advanced text manipulation and data splitting toolsLightweight, fast, and completely free to use for everyday data analysisFamiliar tabbed interface that requires zero learning curve

Frequently Asked Questions

What if my version of Excel doesn't support the TEXTSPLIT function?

If you are using an older version of Excel, you will not have access to TEXTSPLIT. Instead, you can use the 'Text to Columns' feature located under the Data tab to split your text, or use a combination of MID, FIND, and LEN functions to extract specific substrings.

How do I remove extra spaces after splitting the text?

You can wrap your cell references or the entire resulting array in the TRIM function. TRIM automatically removes leading, trailing, and excessive internal spaces from text strings.

Can I use formulas to extract only the numbers from these descriptions?

Extracting only numbers from mixed text can be complex. While you can use nested SUBSTITUTE and array formulas to strip non-numeric characters, it is often easier to use Flash Fill (Ctrl + E) by typing the desired number manually in the first row and letting Excel pattern-match the rest.

Excel & Data Problems