Convert text amounts with known separators

Keep zero, missing values and ambiguous amounts distinct while reconciling totals.

An amount that looks numeric may be stored as text. Removing punctuation can make it numeric while changing its value. The string 1,234 might mean one thousand two hundred thirty-four or a decimal value, depending on the export's convention.

Keep conversion separate from approval

A number returned by a conversion formula is not proof that the source follows your business rules. Agree the currency, decimal separator, grouping convention and treatment of blanks first. The sample review statuses describe these particular input rows; do not copy an accepted status onto new or edited data without reviewing it again. Retain the original amount text, converted value and decision together so a reviewer can explain the final total.

Prepare the six input rows

Download amounts-input.csv and the separate expected amounts. Import the UTF-8 comma-delimited input with amount_text as Text. With headers in row 1, columns A:C are row_id, amount_text and source_format; the six records occupy rows 2:7.

Keep A:C unchanged. Add amount_numeric in D1 and status in E1. Inspect B2 and B3 before continuing: they must retain 1,234.50 and 1.234,50 as source text. If Excel already interpreted them as numbers, reimport with the text type selected first.

Establish the source convention

This fixture uses US to mean a period decimal separator and comma grouping separator. DE means a comma decimal separator and period grouping separator. These are declared conventions for this exercise, not automatic language detection. Import amount_text as Text and keep the source_format column beside it.

The rows include two representations of 1234.50, a negative amount, an explicit zero, a blank and an ambiguous value with an unknown format. Currency is assumed to be the same for the four accepted amounts; a real multi-currency export must be grouped by currency before totals are meaningful.

Convert only the known cases

Use NUMBERVALUE with explicit separators in helper columns. For a known US-formatted amount in B2:

=IF(B2="","",NUMBERVALUE(B2,".",","))

For a known DE-formatted amount:

=IF(B2="","",NUMBERVALUE(B2,",","."))

The blank guard is intentional. Keep the raw value and a separate status column so a missing amount is not silently turned into a reported zero. Route unknown source formats to review without evaluating either formula. If your Excel uses semicolons between arguments, change argument separators while leaving the quoted decimal and grouping characters intact.

A conversion error should remain visible during review. Wrapping every formula in IFERROR with a zero fallback makes bad input indistinguishable from a legitimate zero transaction. For a recurring process, create a rejected-record table with the original text and the reason.

Apply the right formula to each sample row

Do not fill the US formula down the whole mixed-format column. For this small exercise, choose the formula from source_format and use this cell map. On a narrow screen, scroll the table sideways to read the complete formula and status:

Enter each formula in the indicated cell and type the status in the adjacent E cell. Format D2:D5 with two decimal places for comparison. In spare cells, =SUM(D2:D7) should return 2444 and =COUNT(D2:D7) should return 4. A count of 6 means the missing or unknown-format cases have been converted when they should not be.

Verify values and totals

The accepted total is 2444.00 across four numeric values. The expected results file is an audit reference, not an instruction to overwrite the source. Compare the negative sign, the explicit zero and both large amounts individually as well as summing them.

Our fixture check calculates the expected total using decimal arithmetic. Excel's number formatting controls display, not the underlying precision. Set a documented rounding rule when you process calculated money values, and reconcile at the precision required by the receiving system.

Handle additional symbols deliberately

These examples do not include currency symbols, parentheses for negative amounts, percentages or embedded spaces. Add a rule for each form before accepting it. For example, removing parentheses without changing the sign converts a debit into a positive amount. Reject an unrecognized form rather than expanding replacements until the error disappears.

Reference

If the same report also contains mixed date conventions, validate date text using the declared source format before grouping amounts by reporting date.

Microsoft describes explicit separators and empty-text behavior in NUMBERVALUE.

Verification scope

On September 30, 2026, Excel for Mac 16.112.2 evaluated these NUMBERVALUE formulas on sample strings entered after their cells had been formatted as Text. Results were 1234.5, 1234.5, -25 and 0; the total was 2444 and COUNT was 4. The missing and unknown-format cases stayed blank in the numeric output and were handled separately. This validates the formulas and reconciliation for these inputs, not automatic locale detection or every CSV importer.