How to Extract Values from Raw Data and Return Them as a List in Excel
Question details
The user needs a method to parse structured raw text to extract specific pairs of data, such as ticker symbols and company names, and format the output as a clean two-column list on another worksheet.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Organizing raw imported text strings into a usable, multi-column tabular format for further data analysis.
- Observed behavior
- Raw data strings need to be dynamically filtered, stripped of unnecessary characters, and wrapped into rows to create a structured dataset.
Review your raw data to identify the consistent structural patterns or delimiters (such as colons or quotation marks) so that text functions can accurately target the information you want to extract.
Use Nested Text and Array Formulas
Combine dynamic array functions like FILTER, TEXTBEFORE, TEXTAFTER, and WRAPROWS to isolate the required text and restructure it into a multi-column array.
By utilizing modern dynamic array formulas, you can extract text fragments from a messy dataset and instantly reorganize them without manual copying and pasting. The WRAPROWS function will take a single column or row of extracted data and wrap it into the specified number of columns.
Identify the exact cell range containing your imported raw text. For example, your data might reside in 'Raw data'!$D$5:$D$48.
Click the first cell of your target worksheet where the structured list should begin, and enter the following nested formula: =WRAPROWS(TEXTBEFORE(TEXTAFTER(TEXTAFTER(TRIM(FILTER('Raw data'!$D$5:$D$48,ISNUMBER(SEARCH("Ti*",'Raw data'!D5:D48)))),": "),""""),""""),2)
Adjust the delimiter ": " or search terms like "Ti*" to match the actual structure of your raw data. Change the number 2 at the end of the formula if you need a different number of columns.
Extract and Organize Raw Data Automatically with WPS Spreadsheet
WPS Spreadsheet provides powerful support for dynamic array formulas, enabling you to seamlessly clean, parse, and reorganize raw imported text without relying on complex VBA macros.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your raw text data.
- 2. Select the destination: Navigate to a clean worksheet and select the top-left cell where you want your new two-column list to appear.
- 3. Insert the array formula: Paste the nested extraction formula into the formula bar, adjust the cell references to match your raw data, and press Enter to instantly generate your structured list.
Frequently Asked Questions
Why does my WRAPROWS formula return a #NAME? error?
This error generally occurs if you are using an older version of your spreadsheet software that does not support modern dynamic array functions. Try updating your software to the latest version.
Can I extract values separated by commas instead of quotation marks?
Yes. You can customize the delimiter parameters within the TEXTBEFORE and TEXTAFTER functions. Simply replace the quotation marks (""") in the formula with a comma (",") or any other specific character present in your text.
How do I change the output list from two columns to three columns?
The number of columns is controlled by the wrap_count argument at the end of the WRAPROWS function. In the example formula, changing the final '2' to a '3' will force the extracted text to wrap into three columns instead of two.
