The Problem
Price data is often stored as text instead of clean numbers. A value may include currency symbols, labels like USD, or extra words such as each. That looks readable, but it stops Excel from using the values properly in calculations.
This usually happens after exports from invoices, shopping carts, PDFs, or manually typed notes. The problem is that a human can read Price: 15.50, but a spreadsheet needs the number by itself before it can sum, compare, or chart the values.
The flow below shows the cleanup path. The unwanted text is removed first, then the remaining numeric text is converted into a real number that can be audited.
The Problem: Readable Price Text Is Not Always Calculable Cleanup must leave behind real numbers, not just nicer-looking strings.
In this workbook, each invoice amount uses a slightly different text pattern. The challenge is to clean each row into a usable number, count the processed records, and sum the cleaned invoice values.
- Known symbols and words need to be removed from the source text.
- The cleaned result should behave like a number, not plain text.
- The final total should prove the cleanup worked.
That makes the sheet useful for actual pricing work. If the cleaned values can be summed, the user knows the cleanup went beyond display and produced usable numeric data.
How We Solve It
Each row has a different kind of extra text, so the formula pattern removes the unwanted piece first and then converts the result into a numeric value. SUBSTITUTE does the cleanup and VALUE finishes the conversion.
Method 1: Remove symbols with SUBSTITUTE
Method 1: Remove known symbols one by one with SUBSTITUTE.
This is the simplest pattern when you know exactly which symbols are in the text. You can nest SUBSTITUTE calls to strip them out before converting the result.
This solves the cleanup problem when the unwanted characters are predictable. Once the dollar signs, commas, or labels are removed, the remaining text is close enough to a real number that Excel can work with it properly.
=SUBSTITUTE(SUBSTITUTE(A2, "$", ""), ",", "")
Method 2: Use NUMBERVALUE for formatted numbers
Method 2: Convert formatted text to a number when the separators are known.
NUMBERVALUE is useful when the text already looks numeric but uses grouping or decimal separators. It is not the main pattern in this challenge, but it is good to know when the problem is more about formatting than extra words.
This solves the conversion problem in cases where the number is mostly clean and the separator style is the main obstacle. It is a strong option when the text does not have extra words, but it still is not being read as a number.
=NUMBERVALUE(A2, ".", ",")
Method 3: Remove trailing words
Method 3: Strip suffix text from the end of a number string.
Some entries only need a short text fragment removed from the front or back. That is still a good fit for SUBSTITUTE, and once the text is gone, VALUE can turn the result into a real number.
This solves the rows where the number itself is fine but extra wording is attached around it. By removing the known text first, the formula leaves behind a value that can be converted and added into the final total.
=VALUE(SUBSTITUTE(A3," USD",""))
Function Explanation
1. SUBSTITUTE
SUBSTITUTE replaces one text fragment with another. In this challenge, the replacement is usually an empty string so the unwanted symbols disappear.
It works best when the unwanted pieces are known in advance, like $, USD, or TOTAL .
2. VALUE
VALUE converts text that looks like a number into an actual number. That matters because a cleaned string is not always treated as numeric until it is converted.
This is what makes the result usable for math. Without conversion, the cleaned value may look right but still fail in totals or calculations.
Learn more this functionVALUE
3. SUM
SUM adds the cleaned values together. It is the final proof that the cleanup worked, because text that was not converted properly would break the total.
That is why the summary is part of the exercise. It checks whether the row cleanup produced values that can actually be used in reporting.
Learn more this functionSUM
The key idea is that the cleanup has to match the real pattern in the source text. When the unwanted pieces differ from row to row, the formula often needs to target each pattern directly.
Clean each invoice string into a real number, then finish the summary so the worksheet shows how many invoice records were processed and what the total invoice sum is after cleanup.