How to Extract Price from Text to Calculate Ticket Sales in Excel
Question details
The user needs to extract a numeric price embedded within a text string in one cell and multiply it by a quantity in another cell to calculate total sales.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Calculating total ticket sales when the ticket price is combined with text descriptions in a single cell.
- Observed behavior
- A formula is required to isolate the numeric price from the text string before performing multiplication, as direct multiplication with a text cell results in an error.
Review your data column to identify a consistent pattern or delimiter separating the text and the price, such as a dollar sign ($), hyphen (-), or space, as this determines the exact formula structure needed.
Extract Price Using TEXTAFTER and VALUE Functions
Ideal for Microsoft 365 or modern Excel versions where the price follows a specific delimiter like a currency symbol.
The TEXTAFTER function is a modern Excel feature that extracts text occurring after a specified character. By wrapping it in the VALUE function, the extracted text is immediately converted into a workable number for math operations.
Look at the cell containing the ticket description and price to determine the character immediately preceding the price (e.g., "$").
Click on the total sales cell. Assuming A2 contains the mixed text and B2 contains the quantity, input the formula: =VALUE(TEXTAFTER(A2,"$"))*B2
Press Enter to calculate the result, then click and drag the fill handle at the bottom-right of the cell down to apply the formula to the remaining rows.

Extract Price Using FIND, MID, and VALUE Functions
A universal method that works perfectly across all versions of Excel, including older legacy editions.
Easily Extract and Calculate Sales Data with WPS Spreadsheet
WPS Spreadsheet provides robust support for advanced text manipulation and math functions, allowing you to seamlessly extract prices from mixed text strings and automate your sales calculations.
- 1. Open the data file: Launch WPS Office and open your spreadsheet containing the ticket sales data.
- 2. Input the extraction formula: Click on the target cell for total sales and enter the appropriate extraction formula, such as =VALUE(MID(A2, FIND("$", A2)+1, 10))*B2.
- 3. Batch calculate: Press Enter to get the first result, then click and drag the fill handle at the bottom-right corner of the cell to calculate totals for all tickets instantly.

Frequently Asked Questions
Why do I get a #VALUE! error when multiplying text and numbers in Excel?
The #VALUE! error occurs because Excel cannot perform mathematical operations directly on cells containing text strings. You must isolate the numeric portion using text functions and convert it to a true number using the VALUE() function before performing multiplication.
How can I extract numbers if there is no clear delimiter like a dollar sign?
If there is no specific character separating the text and the number, you can use Excel's Flash Fill feature. Manually type the price into the adjacent column for the first row, select the next cell down, and press Ctrl+E. Excel will recognize the pattern and automatically extract the numbers for the rest of the column.
Does WPS Spreadsheet support the same formulas as Excel for this task?
Yes, WPS Spreadsheet is highly compatible with Microsoft Excel formulas. Classic functions required for this task, such as FIND, MID, VALUE, LEFT, and RIGHT, are fully supported and behave exactly as they do in Excel.




