WPS Spreadsheet: Convert Excel Cr and Dr Number Formats
Question details
The user needs to convert accounting values ending in Cr or Dr into usable numeric values.
- Product
- Microsoft Excel and WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Accounting data uses Cr and Dr suffixes instead of standard negative signs, and the values need to be converted for calculations.
- Observed behavior
- Entries containing Cr or Dr may be stored as text, preventing normal formulas from calculating totals correctly.
Confirm whether Cr should represent a negative value and Dr should represent a positive value in your accounting system. Check a few source cells for spaces, commas, or other currency symbols before applying the formula.
Convert Cr and Dr entries with a helper formula
Use this method when you need to convert individual cells or fill a conversion formula down a column.
The formula treats Cr as a negative value and Dr as a positive value. It also removes the suffix before NUMBERVALUE converts the remaining text into a number.
In an empty cell next to the source value in A1, enter =IF(ISNUMBER(SEARCH("Cr",A1)),-NUMBERVALUE(TRIM(SUBSTITUTE(A1,"Cr",""))),NUMBERVALUE(TRIM(SUBSTITUTE(A1,"Dr","")))) and press Enter.
Select the formula cell, then drag its fill handle down beside the remaining Cr and Dr entries.
Select a converted result and use Home > Number Format to apply Number or Accounting formatting. The result should align right and work in SUM formulas.
Total a range containing Cr and Dr values
Use an array formula when you need to calculate a total directly from a range of accounting text values.
Convert Cr and Dr accounting text in WPS Spreadsheet
WPS Spreadsheet can convert Cr and Dr suffixes into numeric values with familiar formulas, fill the conversion down a column, and calculate totals in a compatible spreadsheet workflow.
- 1. Open the spreadsheet: Launch WPS Office, choose Spreadsheet, and open the workbook containing the Cr and Dr entries.
- 2. Add a helper column: Click an empty cell beside the source data and enter =IF(ISNUMBER(SEARCH("Cr",A1)),-NUMBERVALUE(TRIM(SUBSTITUTE(A1,"Cr",""))),NUMBERVALUE(TRIM(SUBSTITUTE(A1,"Dr","")))) .
- 3. Fill the conversion formula: Press Enter, select the formula cell, and drag the fill handle down to convert the rest of the accounting entries.
- 4. Calculate and format the total: Use SUM on the converted range, then select Home > Number Format and choose Number or Accounting.
Frequently Asked Questions
Why does Excel treat Cr and Dr values as text?
The Cr or Dr suffix makes the cell a text string rather than a standard numeric value. Remove the suffix with SUBSTITUTE and convert the result with NUMBERVALUE before using SUM or other calculations.
How do I convert a single Cr value to a negative number?
If the value is in A1, enter =NUMBERVALUE(SUBSTITUTE(A1,"Cr","-")) in another cell. This changes a value such as 1,250Cr into -1,250.
Can I convert both Cr and Dr in one formula?
Yes. Use =IF(ISNUMBER(SEARCH("Cr",A1)),-NUMBERVALUE(TRIM(SUBSTITUTE(A1,"Cr",""))),NUMBERVALUE(TRIM(SUBSTITUTE(A1,"Dr","")))) to make Cr negative and Dr positive.
Why does the range formula require Ctrl+Shift+Enter in older Excel versions?
Older Excel versions evaluate formulas that process multiple cells as array formulas. After entering the range formula, press Ctrl+Shift+Enter instead of Enter so Excel evaluates every cell in the range.
What if my accounting system uses Dr for negative values?
Reverse the sign logic in the IF formula. Replace the negative NUMBERVALUE branch for Cr with the negative branch for Dr, according to the convention used by your accounting system.
