How to Extract Item Numbers and Prices from Excel Text
Question details
The user needs to extract specific item numbers and price values from a concatenated product description string within a single Excel cell.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Separating mixed strings containing product descriptions, item numbers, and varying dollar amounts into distinct columns for data analysis.
- Observed behavior
- Item numbers and prices are combined in a single text string, requiring extraction into separate columns based on spaces, delimiters, or specific currency symbols.
Ensure your data follows a consistent pattern, such as item numbers always appearing before the first space, and prices being consistently marked with a specific currency symbol like a dollar sign.
Use Flash Fill for Quick Data Extraction
Flash Fill automatically recognizes patterns in your data and fills in the rest of the column without requiring complex text formulas.
If your data layout is consistent across rows, Flash Fill is the fastest and easiest way to split mixed text strings. It learns from your manual entry and applies the same extraction logic downward.
Insert a new blank column directly next to your source data to hold the extracted item numbers.
In the first empty cell of the new column, manually type the correct item number exactly as it appears in the source text, then press Enter.
Select the cell immediately below the one you just typed in. Go to the 'Data' tab on the top ribbon and click 'Flash Fill', or simply press the keyboard shortcut Ctrl+E.
Create another new column for the prices. Type the first price manually, press Enter, and press Ctrl+E again to extract the remaining prices.
Use Excel Text Formulas (LEFT, FIND, MID)
Utilize Excel's built-in text functions to locate spaces or currency symbols and extract the required substrings precisely.
Easily Extract Text and Numbers with WPS Spreadsheet
WPS Spreadsheet offers powerful text functions and intelligent Flash Fill capabilities to help you effortlessly separate concatenated product descriptions, item numbers, and prices.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office, open your spreadsheet file, and ensure your concatenated data is neatly arranged in a single column.
- 2. Use Flash Fill: Type the desired item number or price in the adjacent cell for the first row to establish a pattern.
- 3. Execute the extraction: Select the cell directly below your manual entry and press Ctrl+E to activate Flash Fill, instantly filling the entire column.
- 4. Utilize text formulas: Alternatively, use the formula bar to input functions like =LEFT(A2,FIND(" ",A2)-1) for more precise and dynamic data extraction.
Frequently Asked Questions
Why is Flash Fill not working correctly on my data?
Flash Fill relies on consistent patterns. If your data has irregular spacing, missing prices, or vastly different formats in some rows, Flash Fill might not recognize the underlying pattern. In such cases, manually correcting a few mistaken rows can teach the tool the new pattern, or you can switch to using precise text formulas.
How do I extract text located after a specific character?
You can use the MID and FIND functions together. For example, the formula =MID(A2, FIND("-", A2)+1, 100) will find a hyphen in cell A2 and extract the next 100 characters following it.
Can I extract numbers only from a mixed text string?
Extracting purely numerical values from a highly irregular string usually requires complex array formulas or VBA/Macros. However, if the numbers follow a specific delimiter (like a $ sign or a colon), the MID and FIND functions can easily isolate and extract them.
Are Excel text formulas fully supported in WPS Spreadsheet?
Yes, WPS Spreadsheet fully supports all standard Excel text manipulation formulas, including LEFT, RIGHT, MID, FIND, SEARCH, and SUBSTITUTE, ensuring complete compatibility when opening your Excel workbooks.
