The export that sums to zero
A sales export pasted from a supplier portal fills A1:H4001. Column A holds Order ID, column F holds Amount showing 12,480.00 in every populated cell. Selecting F2:F4001 shows 'Count: 4000' in the status bar and no Sum, and =SUM(F2:F4001) returns 0. A lookup =VLOOKUP(10234,A2:H4001,8,FALSE) returns #N/A although 10234 is plainly visible in column A. Sorting Order ID ascending gives 1000, 10000, 1001, 1002. Some IDs were typed with a leading apostrophe, and pasting a sample into Notepad shows trailing spaces after several of them. The month-end report is due tomorrow and needs the totals plus three lookups.
1. Give two checks that distinguish text-stored numbers from real numbers, then two conversion methods with the risk each one carries.
Read the worked answer
Checks.
- Select F2:F4001 and read the status bar: Count with no Sum means nothing numeric was found. Compare =COUNT(F2:F4001) with =COUNTA(F2:F4001) - 0 against 4000 is conclusive.
- =ISNUMBER(F2) returns FALSE for text. The green triangle in the corner is the same signal.
- Alignment is a hint only. Right-aligned text exists whenever a template or a manual format set it, so never diagnose from alignment alone.
Methods.
- Text to Columns, no delimiter, Finish - converts the column in place and is the fastest for 4,000 rows. Risk: it destroys leading zeros, so an ID like 00712 becomes 712 with no way back except re-importing.
- Paste Special > Multiply by a cell containing 1. Risk: it corrupts genuine dates (they are numbers underneath), and it does nothing if the cell contains a trailing space.
- Helper column =VALUE(TRIM(A2)) or =--A2, then copy and Paste Values. Safest because the original survives, but it leaves stray columns and links if you forget the paste-values step.
For trailing spaces use TRIM, and SUBSTITUTE(A2,CHAR(160),"") for non-breaking spaces, which TRIM ignores.
2. Explain the sort order (1000, 10000, 1001, 1002) and why the VLOOKUP fails. What must be true before the lookup can match?
Read the worked answer
Sort. Text is compared character by character, not by magnitude. '1000' is a prefix of '10000', so the shorter string sorts first. Between '10000' and '1001' the fourth characters decide it: '0' is less than '1', so '10000' comes first. A numeric sort would give 1000, 1001, 1002, 10000.
Lookup. =VLOOKUP(10234,...) offers a number; column A holds text. Excel does not coerce for you, so no match exists and the exact-match form returns #N/A. A trailing space breaks the comparison even between two text values, which is why some IDs would still fail after conversion.
Prerequisites for a working match:
- Lookup value and the first column of the table must be the same data type.
- Both sides must be free of leading and trailing spaces.
- The lookup column must be the leftmost column of the range you pass.
- Exact match (FALSE or 0) for IDs; approximate match will return a plausible wrong row.
The 'Convert to number' shortcut acts on the selected range only - fine for three cells, unsafe for 4,000 because it will also convert genuine codes.