How to Extract Fuel Type and Amount from Text Strings in Excel
Question details
Extract specific fuel type codes and their corresponding fuel amounts from a longer expense description string.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Parsing mixed text and numbers within single cells from expense logs to organize fuel data into separate columns.
- Observed behavior
- The fuel type (e.g., DSL, UNL) and amount with units (e.g., 63.74L, 19.173GAL) are embedded in a continuous text string, requiring extraction formulas to isolate them.
Ensure your expense description data is organized in a consistent column, and note that the formulas for extracting the amount require a modern version of Excel that supports dynamic array functions like TEXTSPLIT and TAKE.
Extract the Fuel Type Using IFS and SEARCH
Use logical and text search functions to look for specific keywords in the cell and return the exact match.
The SEARCH function looks for the position of specific strings like "DSL" or "UNL". Wrapping it in ISNUMBER and IFS allows Excel to return the matched string cleanly when found.
Click on an empty cell next to your expense description (e.g., cell B2) where you want the fuel type to appear.
Type the following formula into the formula bar: =IFS(ISNUMBER(SEARCH("DSL",A2)),"DSL",ISNUMBER(SEARCH("UNL",A2)),"UNL",TRUE,"")
Press Enter to execute the formula, then drag the fill handle down to apply this extraction logic to the rest of the rows in your column.
Extract the Fuel Amount with Units as Text
Use text separation functions to pull out the precise amount and its measurement unit from the string.
Convert the Extracted Amount into a Pure Number
Strip out the text units (like L or GAL) from your extracted amount so you can use it in mathematical calculations.
Extract Text and Numbers Easily in WPS Spreadsheet
WPS Spreadsheet provides comprehensive support for logical and text functions like IFS, SEARCH, and SUBSTITUTE. Easily parse complex strings, expense descriptions, and messy logs without needing advanced programming skills.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your expense descriptions.
- 2. Select the target cell: Click the cell where you want to output the extracted fuel type or amount.
- 3. Input your extraction formula: Type your chosen formula (like =IFS(ISNUMBER(SEARCH...)) directly into the formula bar at the top.
- 4. Fill down the column: Press Enter, then double-click the small green square at the bottom-right of the cell to auto-fill the formula down the entire column.
Frequently Asked Questions
What if my version of Excel doesn't support the TEXTSPLIT function?
If you are using an older version of Excel, functions like TEXTSPLIT and TAKE will not work. Instead, you can use a combination of LEFT, RIGHT, MID, and FIND functions to manually locate the spaces in the string and extract the desired text segment.
How can I sum up the fuel amounts once they are extracted?
To perform calculations, the data must be formatted as numbers, not text. Use the third solution provided, which utilizes the VALUE and SUBSTITUTE functions to remove text units like "L" or "GAL" and convert the remaining string into a pure, calculable number. You can then use the SUM function on that column.
Will the IFS formula work if I need to add more fuel types later?
Yes, the IFS function is highly scalable. You can continually add pairs of conditions and results to the formula. For example, to add an "E85" fuel type, simply insert ISNUMBER(SEARCH("E85",A2)),"E85" into the existing IFS structure before the TRUE condition.
