Declare the amount convention first
The sales sample uses RM as a prefix, a decimal point, and optional comma thousands separators. A discount is an amount per line. These assumptions are necessary: 1,234.50 and 1.234,50 can represent the same amount under different conventions, while 12,50 is not valid under the convention chosen here.
Work with the example
Amount practice data ↓Prepared order values ↓All records are fictional. Import CSV identifier columns as Text. Quoted CSV fields do not define spreadsheet column types.
Do not remove every punctuation mark and hope the result is a number. That can turn a negative into a positive or a decimal into a value a hundred times larger.
Use one visible cleanup step per meaning
For a source value in A2, first remove the RM prefix and outer spaces in B2. Then detect the sample's parentheses convention in C2 and parse the numeric part in D2. This example assumes a valid amount string using the declared separators; review invalid strings before applying it to a full file.
B2 = TRIM(SUBSTITUTE(A2,"RM",""))
C2 = IF(LEFT(B2,1)="(",-1,1)
D2 = IF(B2="","",
NUMBERVALUE(SUBSTITUTE(SUBSTITUTE(B2,"(",""),")",""),".",",")*C2)The formula is a conversion method for reviewed inputs, not a strict validator. NUMBERVALUE may accept separator placements you did not intend. The practice row 12,50 must remain unresolved until its source convention is known. Never silently accept its parsed result as the intended amount.
Check missing, zero, and negative separately
| Source | Expected result | Why |
|---|---|---|
| RM 1,234.50 | 1234.50 | Known thousands and decimal convention. |
| (RM25.00) | -25.00 | Parentheses are explicitly defined as negative in this sample. |
| 0.00 | 0.00 | A valid numeric zero. |
| Blank | Blank | Missing input is not a zero. |
| 12,50 | Unresolved | Its intended decimal convention has not been established. |
Do not wrap the entire conversion in IFERROR(...,0). An invalid amount would disappear into an apparently legitimate zero. Leave the exception visible and resolve it at the source or in a reviewed mapping.
Reconcile line math against a second total
For the verified week's eligible lines, quantities multiplied by unit prices sum to RM286.60. Line discounts sum to RM19.00. Net sales are RM267.60. Sum the included lines independently and check that the gross-minus-discount bridge reaches the same answer.
Keep customer identifiers out of the amount conversion. A phone or order number that looks numeric should remain text. After conversion, check the first row, a discounted row, a zero discount, and a boundary record. Round using the rule required by the source; this example uses two-decimal amounts and per-line arithmetic.
Sources & further reading
The sample data and worked calculations are original instructional examples. Spreadsheet menus and supported features depend on your installed version. See the verification method for the checks performed.
Continue the workflow