Function Problems

How to Extract Price from Text to Calculate Ticket Sales in Excel

Tauseeq MagsiTauseeq Magsi Nov 11, 2026 869 views

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.

How to Extract Price from Text to Calculate Ticket Sales in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Identify the delimiter

Look at the cell containing the ticket description and price to determine the character immediately preceding the price (e.g., "$").

2
Enter the formula

Click on the total sales cell. Assuming A2 contains the mixed text and B2 contains the quantity, input the formula: =VALUE(TEXTAFTER(A2,"$"))*B2

3
Apply and calculate

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 TEXTAFTER and VALUE Functions
Version Compatibility: The TEXTAFTER function is available in Excel for Microsoft 365 and Excel 2024. For older versions, you will need to use the FIND and MID functions instead.
Efficient Data Extraction

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. 1. Open the data file: Launch WPS Office and open your spreadsheet containing the ticket sales data.
  2. 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. 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.
Fully compatible with Microsoft Excel formulas and .xlsx file formats.Built-in classic text functions like FIND, MID, RIGHT, and VALUE to handle messy data.Lightweight, fast, and completely free to download and use.
microsoft office alternative - wps office

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.