The total is too low after conversion
Replacing invalid text with zero hides a data problem. Use the review label and inspect those rows.
Convert imported text into numbers
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Convert A2:A7 into B2:B7 using a dot decimal and comma group separator. Keep empty inputs blank and return Check value for invalid text.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: B2, B3, B4, B5, B6, B7. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Convert amounts with an explicit decimal convention, keep missing values blank, and flag text that cannot be parsed.
THE QUICK ANSWER01
NUMBERVALUE uses the separators supplied in the formula. IF preserves empty inputs, while IFERROR returns Check value for inputs that cannot be converted.
ƒx
=IF(TRIM(A2)="","",IFERROR(NUMBERVALUE(A2,".",","),"Check value"))For the example data
B2 1250.5B3 -35.75B4 32B5 42B6 Check valueB7
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
The separator choice is part of the data
This practice assumes a dot decimal and comma group separator. An input such as 1.234,56 uses a different convention and is flagged. Change the parsing rule for that dataset instead of mixing regional formats in the same column.
Percent signs are numeric too
A trailing percent sign scales the parsed number by one hundredth: text 15% becomes 0.15. This rule accepts decimal amounts and percentages, not currency symbols or dates.
Do not convert every text column
Product codes, account identifiers and postal codes may need their leading zeros. Keep those as text. Conversion is appropriate only when the field represents a quantity on which arithmetic makes sense.
Read the full step-by-step walkthrough
- Distinguish a value from its appearance
An imported amount can look numeric while remaining text. A conversion should produce a number that later formulas can use.
- Choose the decimal convention
Here a dot separates the fraction and a comma groups digits. Pass those separators explicitly.
NUMBERVALUE(A2,".",",") - Keep missing values missing
A blank input should stay blank, not become a recorded zero. Test for an empty trimmed input first.
IF(TRIM(A2)="","",…) - Flag values that need attention
Invalid text gets Check value. Do not quietly turn it into zero, which would change later totals.
IFERROR(…,"Check value") - Review identifiers before converting
Converting text 0032 produces the number 32. That is correct for an amount but usually wrong for an identifier whose leading zeros matter.
Things to watch
European notation is misread
This formula specifies a dot decimal and comma group separator. Confirm the source convention first.
An ID loses zeros
Number conversion intentionally removes insignificant numeric zeros. Leave identifiers as text.
About this example
Published examples, formula filling and practice checks were tested in Chrome on October 13, 2026. Tests cover the supplied datasets and stated input rules, not every possible formula. These workbooks have not yet been independently recalculated in Microsoft Excel or Google Sheets.
Content version 2. Practice checks compare calculated results across different datasets; they do not certify every possible formula.