Function Problems

How to Clean Excel Text Using MID, SUBSTITUTE, REDUCE, and LAMBDA

Huda Qurayshi Oct 1, 2026 869 views

Question details

The user needs to clean messy text data in a spreadsheet by extracting text located after the first slash and subsequently removing all spaces, hyphens, and alphabetical characters.

How to Clean Excel Text Using MID, SUBSTITUTE, REDUCE, and LAMBDA

Product: Spreadsheets
Device & OS: not provided
Scenario: Processing complex string data where a substring must be extracted and then heavily sanitized by stripping out specific punctuation marks and all letters.
Observed behavior: A complex combination of dynamic array and text functions (LET, SEARCH, MID, REDUCE, LAMBDA, VSTACK, CHAR, SEQUENCE, SUBSTITUTE) is required to parse and clean the text in a single formula step.

Before you start

Ensure your spreadsheet software is up to date and supports dynamic array functions such as LET, REDUCE, and LAMBDA, as older versions will not process these formulas.

Solution 1Recommended

Use a Combined Formula with LET, REDUCE, and LAMBDA

This solution uses a single, advanced formula to locate the slash, extract the text, and iteratively strip out unwanted characters.

The LET function defines variables to keep the formula readable. SEARCH and MID handle extracting the text after the slash. REDUCE and LAMBDA iterate over an array of characters (spaces, hyphens, and a-z letters generated by SEQUENCE) to substitute them with blanks.

Select the target cell

Click on the cell where you want the cleaned text to be displayed.

Enter the advanced formula

Type or paste the following formula: =LET(start,SEARCH("/",C3)+1,t,MID(C3,start,LEN(C3)-start),REDUCE(t,VSTACK(CHAR({32;45}),CHAR(SEQUENCE(26,,97,1))),LAMBDA(x,y,SUBSTITUTE(LOWER(x),y,""))))

Apply and fill

Press Enter to execute the formula. If you have multiple rows of data, drag the fill handle from the bottom-right corner of the cell to apply the formula down the column.

Use a Combined Formula with LET, REDUCE, and LAMBDA

Adjust Cell References: Make sure to replace 'C3' in the formula with the actual cell reference that contains your raw text data.

Solution 2

Extract Text Using Helper Columns

If you are using software that does not support REDUCE or LAMBDA, you can break the text extraction down into step-by-step helper columns.

Clean Complex Text Effortlessly in WPS Spreadsheets

WPS Spreadsheets provides powerful text manipulation capabilities and full support for advanced array functions, making it simple to process complex data strings and extract exactly what you need.

1. Open your dataset: Launch WPS Spreadsheets and open the workbook containing the messy text data.
2. Input the formula: Click on the cell for your output and paste your combined text-cleaning formula into the Formula Bar.
3. Calculate and evaluate: Press Enter to instantly view the cleaned text, utilizing WPS Office's robust formula calculation engine.

Fully compatible with Microsoft Excel formula syntaxSupports modern dynamic array functions for advanced text extractionLightweight and runs smoothly on multiple operating systemsFree to use for everyday data cleaning and spreadsheet tasks

microsoft office alternative - wps office

Frequently Asked Questions

What does the REDUCE function do in this text cleaning formula?

REDUCE applies a LAMBDA function to each element in an array, carrying the result forward to the next iteration. In this context, it iterates through a list of unwanted characters (spaces, hyphens, and letters) and systematically substitutes them with blanks.

Why use VSTACK and SEQUENCE for text substitution?

VSTACK is used to combine different character arrays into one continuous list. CHAR(SEQUENCE(26,,97,1)) dynamically generates all 26 lowercase alphabet letters. Stacking this with spaces and hyphens creates a complete list of characters for the REDUCE function to eliminate.

Will this formula work on older spreadsheet versions?

No. Functions like LAMBDA, REDUCE, and VSTACK are modern dynamic array functions. They are available in Microsoft 365 and newer software like the latest version of WPS Office. Older versions will display a #NAME? error.

Excel & Data Problems