How to Classify and Extract Reference Codes with Excel Formulas
Question details
The user needs to classify and extract specific reference codes from a column containing mixed text using Excel formulas based on prefixes and length rules.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting and cleaning specific reference codes that are mixed within other text strings (like dates and initials) based on predefined prefixes and length rules.
- Observed behavior
- The source data contains multiple elements in a single cell, requiring the reference codes starting with 80, 10, 11, or DO to be classified into one column and their exact values extracted into another column.
Insert two blank columns next to your source data to hold the classification tags and the extracted reference codes, and identify the exact cell reference (e.g., B1) where your data begins.
Use Nested IF, SEARCH, and MID Formulas for Classification and Extraction
This method uses standard Excel text functions to locate prefixes, validate lengths, and extract the specific reference codes into separate columns.
By combining SEARCH to find the prefix position, MID to extract the characters, and LEN to verify the exact length, you can accurately classify the data. A subsequent formula can then extract the specific code based on that classification.
In the first blank column (e.g., Column C), enter the following formula to classify the data type based on the prefix and length: =IF(AND(ISNUMBER(SEARCH("80",B1)),LEN(MID(B1,SEARCH("80",B1),9))=9),"Type 1",IF(AND(OR(ISNUMBER(SEARCH("10",B1)),ISNUMBER(SEARCH("11",B1))),OR(LEN(MID(B1,SEARCH("10",B1),9))=9,LEN(MID(B1,SEARCH("11",B1),12))=12)),"Type 2",IF(AND(ISNUMBER(SEARCH("DO",B1)),LEN(MID(B1,SEARCH("DO",B1)+2,6))=6),"Type 3","Unknown")))
In the next column (e.g., Column D), enter the extraction formula that pulls the code based on the classification from Column C: =IF(C1="Type 1",MID(B1,SEARCH("80",B1),9),IF(C1="Type 2",IF(ISNUMBER(SEARCH("10",B1)),MID(B1,SEARCH("10",B1),IF(LEN(MID(B1,SEARCH("10",B1),12))=12,12,9)),MID(B1,SEARCH("11",B1),IF(LEN(MID(B1,SEARCH("11",B1),12))=12,12,9))),IF(C1="Type 3",MID(B1,SEARCH("DO",B1)+2,6),"")))
Select the cells containing your new formulas in columns C and D. Click and drag the fill handle (the small square at the bottom-right corner of the selection) down to apply the formulas to the rest of your dataset.
Extract and Classify Text Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced text functions like SEARCH, MID, and IF, allowing you to seamlessly classify and extract complex reference codes. It offers a highly compatible and lightweight environment for all your data analysis needs.
- 1. Open Your Data: Launch WPS Spreadsheet and open the file containing your mixed data.
- 2. Create Helper Columns: Insert two new columns next to your data for the classification tags and extracted codes.
- 3. Enter the Formulas: Paste the nested IF, SEARCH, and MID formulas into the respective columns for the first row of your data.
- 4. Fill Down: Double-click the fill handle on the selected cells to automatically apply the formulas to your entire dataset.
Frequently Asked Questions
Why does the SEARCH function return an error if the prefix is missing?
The SEARCH function returns a #VALUE! error when it cannot find the target string within the text. Wrapping the SEARCH function in ISNUMBER() prevents the error by returning FALSE instead, allowing the nested IF function to proceed to the next condition smoothly.
Can I use Flash Fill instead of formulas to extract the codes?
Flash Fill can sometimes recognize patterns and extract text automatically. However, for complex rules involving specific lengths and multiple prefixes mixed randomly within strings, nested formulas provide much more reliable and accurate results.
How do I ensure only numbers are extracted after the prefix?
While SEARCH and MID extract the text string, you can add further validation using the VALUE function or checking the output with ISNUMBER to confirm that the extracted substring contains only digits and no random letters.
