How to Extract Colors from Product Descriptions in Excel Using LAMBDA
Question details
The user needs a reliable method to extract product colors from large datasets of text descriptions without relying on Flash Fill.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting specific color words from complex product descriptions containing extra words like filter types or finishes.
- Observed behavior
- The user requires an accurate extraction using a recursive LAMBDA function rather than standard FIND or SEARCH combinations, to improve performance on large datasets.
Ensure you are using a recent version of Microsoft 365 or a compatible spreadsheet software that supports dynamic array functions and LAMBDA. Create a separate, clean column listing all the approved colors you wish to extract to serve as your reference range.
Create a Recursive LAMBDA Function to Match and Extract Colors
Define a custom LAMBDA function that recursively checks a product description against a provided list of colors until it finds a match.
This approach creates a custom recursive LAMBDA function called ExtractColor. It avoids utilizing traditional SEARCH or FIND functions, which can be computationally intensive on massive datasets. Instead, it systematically checks the text against an approved list of colors and immediately stops when a match is verified.
Ensure your product descriptions are in a single column (e.g., named 'texts'). Create a separate list of colors you want to find and define that range as 'ColorsTbl'.
Navigate to the Formulas tab on the ribbon and click on 'Name Manager'. Click 'New' to create a new named function.
In the Name field, type 'ExtractColor'. In the 'Refers to' field, paste the following recursive formula: =LAMBDA(string,list_of_colors, LET(next_color,TAKE(list_of_colors,1), err,ISERROR(next_color), check,ISNUMBER(XMATCH("*"&next_color&"*",string,2)), discard,DROP(list_of_colors,1), IF(check,next_color,IF(err,"",ExtractColor(string,discard)))))
In the cell adjacent to your first product description, type the formula =BYROW(texts, LAMBDA(each_text, ExtractColor(each_text, ColorsTbl))) to spill the extracted colors down the column.
Extract Data Effortlessly with WPS Spreadsheet
WPS Office provides powerful spreadsheet capabilities, including advanced functions and dynamic arrays, to handle large datasets seamlessly. Process your complex product descriptions and extract crucial data with impressive speed and precision.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and open the document containing your product descriptions.
- 2. Input the dynamic arrays: Navigate to the Formulas tab and utilize the Name Manager to input your custom LAMBDA and dynamic array formulas.
- 3. Process and save: Apply your text extraction formulas across the entire dataset and seamlessly save the results in standard .xlsx format.
Frequently Asked Questions
Why should I use a recursive LAMBDA instead of SEARCH or FIND?
Standard SEARCH or FIND formulas can become extremely complex and slow when cross-referencing against a long array of target words. A recursive LAMBDA stops searching your color list immediately upon finding a match, making it much more efficient for extensive datasets.
What happens if a product description contains multiple colors?
The ExtractColor recursive formula processes the provided 'list_of_colors' sequentially. It will return the first color it successfully matches from your list, based entirely on the top-to-bottom order of the colors in your reference table.
Is the LAMBDA function available in older versions of Excel?
No, the LAMBDA function is exclusive to newer spreadsheet environments like Microsoft 365. If you are using an older version, you will need to rely on alternative methods such as complex nested IFs, INDEX/MATCH array formulas, or custom VBA macros.
