Convert imported quantities and amounts into real Excel numbers without damaging IDs, leading zeros, missing-value markers, or locale-specific separators. Verify every conversion with ISNUMBER.
I imported these Excel values in A2:A5: A2: '125 A3: [space]240[space] A4: N/A A5: 00123 A5 is an identifier whose leading zeros are meaningful, so it must remain text. A2 and A3 are quantities and should become real numbers. A4 must remain a missing-value marker, not become zero. Give me a non-destructive helper-column formula or formulas that: 1. remove incidental apostrophes and spaces only from the quantity rows; 2. convert valid quantity text to numbers; 3. do not turn N/A into zero; 4. preserve 00123 as text; 5. verify converted quantities with ISNUMBER. State the expected value and data type for every row. Do not recommend converting the entire source column in place because it contains mixed business meanings.
Test 1 of 3 — approved PASS and preserved as tested.
My Excel workbook contains amounts imported as text from two locales: A2: 1.234,56 (European format: decimal comma, thousands period) A3: 2.500,00 (European format: decimal comma, thousands period) A4: 1,234.56 (US format: decimal period, thousands comma) Give exact NUMBERVALUE formulas that convert A2 and A3 using their source separators and A4 using its source separators. Do not rely on my computer's regional settings and do not remove punctuation blindly. Also provide an ISNUMBER check for the converted cells and state the exact numeric result for each row. Explain why applying one locale rule to all three rows can silently produce incorrect values.
Test 2 of 3 — approved PASS and preserved as tested.
I copied these amounts from a PDF/web export into Excel: A2: € 1[non-breaking space]250,00 A3: (350,50) A4: — The space in A2 is a non-breaking space, CHAR(160). Decimal separator is a comma. Parentheses mean a negative amount. The em dash means missing data and must not become zero. Design a non-destructive helper-column formula for Microsoft 365 that: 1. removes the euro symbol and CHAR(160); 2. converts European decimal text with NUMBERVALUE; 3. converts parentheses to a negative number; 4. returns a blank or an explicit missing marker for the em dash; 5. does not hide unexpected text as zero. Give the exact expected numeric values and an ISNUMBER verification. If you use LET, explain each intermediate variable briefly.
Test 3 of 3 — approved PASS and preserved as tested.
| Source condition | Risk |
|---|---|
| '125 · 240 · N/A · 00123 | One mixed column contains quantities, a missing marker, and an identifier. |
| Output | Expected result |
|---|---|
| Typed safely | 125 and 240 numeric; N/A unchanged; 00123 preserved as text |
| Source condition | Risk |
|---|---|
| 1.234,56 · 1,234.56 | The same punctuation has different meanings by source locale. |
| Output | Expected result |
|---|---|
| Explicit locale | 1234.56 · 1234.56 using source-specific NUMBERVALUE |
| Source condition | Risk |
|---|---|
| € 1 250,00 · (350,50) · — | Currency, CHAR(160), accounting negative, and missing data. |
| Output | Expected result |
|---|---|
| Clean conversion | 1250 · -350.5 · missing; never silent zero |
Separate quantities, identifiers, and missing-value markers before converting anything.
Create a helper column; do not apply a blanket in-place conversion to mixed imported data.
Remove apostrophes, incidental spaces, currency symbols, or CHAR(160) only where the source specification supports it.
Use VALUE for simple known text and NUMBERVALUE when decimal and thousands separators must be independent of the computer locale.
Use ISNUMBER, compare row counts and control totals, and investigate unexpected text instead of converting it to zero.