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 challenge cleans each row into a real number, then uses those cleaned values to build a total. The main goal is not just removing text, but making sure the result can actually be summed.
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.
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.
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.
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.