Convert Text Currency Values with Extra Commas to Numbers in Excel
Question details
The user needs to convert text-formatted currency values containing incorrect or extra commas into valid numerical values with proper decimal placement.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning up imported financial or currency data where commas are incorrectly used instead of decimal points, preventing mathematical calculations.
- Observed behavior
- Currency values like 6,764,68 are recognized as text strings with extra commas rather than valid numbers with a decimal point.
Ensure that your data consistently has exactly two digits representing the decimal portion at the end of the string before applying bulk formulas.
Use the SUBSTITUTE and VALUE Functions
This method removes all commas from the text string and divides the result by 100 to place the decimal point correctly.
By nesting the SUBSTITUTE function inside the VALUE function, you can strip out the unwanted text characters and convert the remaining string into a calculable number. Dividing by 100 restores the final two digits as the decimal value.
Click on an empty cell next to your first text currency value (for example, cell B1 if your data is in A1).
Type the formula =VALUE(SUBSTITUTE(A1,",","")/100) into the formula bar.
Press Enter to convert the text into a valid number. The value 6,764,68 will now display as 6764.68.
Click and drag the fill handle (the small square at the bottom-right corner of the cell) down to apply this formula to the rest of your data column.
Convert Formula Results to Static Values
After using the formula, you may want to replace the original text data with the new valid numbers to clean up your spreadsheet.
Clean and Format Currency Data Easily with WPS Spreadsheet
WPS Spreadsheet provides powerful text-to-number conversion tools and full support for advanced formulas like SUBSTITUTE and VALUE, making data cleanup fast and accurate.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your text currency values.
- 2. Select an adjacent cell: Click on an empty cell next to the data you want to convert.
- 3. Input the formula: Type =VALUE(SUBSTITUTE(A1,",","")/100) and press Enter.
- 4. Apply to the column: Drag the fill handle downwards to convert the entire column instantly.
Frequently Asked Questions
What if my currency values have a variable number of decimal digits?
The division by 100 method only works if every value has exactly two decimal digits. For variable decimal lengths, you will need a more complex formula using FIND and LEN to locate the position of the final comma and replace only that specific character with a decimal point.
Why does the VALUE function return a #VALUE! error?
This error occurs if the text string contains hidden spaces, non-printable characters, or currency symbols that the function cannot recognize. Use the TRIM or CLEAN functions to remove these characters before applying the VALUE formula.
Can I use the Text to Columns feature to fix this?
Yes, you can use Data > Text to Columns. In the wizard, skip to Step 3, click Advanced, and specify the comma as the decimal separator and the period (or leave blank) as the thousands separator, depending on your specific data format.
