Easy: Leading spaces break COUNTIF
Some SKUs have leading spaces. COUNTIF(A5:A10,"P-200") returns 0 in the broken file even though two rows look like P-200. TRIM the codes.
काही SKU पुढे spaces आहेत. COUNTIF 0 देते जरी दोन rows P-200 दिसतात. TRIM करा.
कुछ SKU में आगे spaces हैं। COUNTIF 0 देता है जबकि दो rows P-200 दिखती हैं। TRIM करें।
Why → What → How
Why: Copy-paste from portals pads IDs. Exact-match functions then miss rows.
What: Six stock rows; three SKUs padded. COUNTIF for P-200 → 0 broken, → 2 fixed (LibreOffice verified).
How:
- Open sheet Stock.
- Find rows where SKU looks right but starts with spaces.
- TRIM or retype codes.
- Confirm COUNTIF → 2.
Ravindra Bagale’s Tip
LEN(A5) vs LEN(TRIM(A5)) exposes hidden spaces fast.
Ravindra Bagale’s Tip – मराठी
LEN आणि LEN(TRIM) लपलेले spaces दाखवतात.
Ravindra Bagale’s Tip – हिंदी
LEN और LEN(TRIM) छिपे spaces दिखाते हैं।
Bugs explained (with mini examples)
Bug 1 — Leading spaces
Bug 2 — Eyes lie
Practice tasks (12)
- Task 1. Widen column A — spot padded SKUs.Hint: Rows with leading spaces.
- Task 2. Compute LEN on a padded SKU.Hint: Longer than clean code.
- Task 3. COUNTIF for P-200 on broken sheet.Hint: 0.
- Task 4. TRIM the SKU column.Hint: Helper or in-place.
- Task 5. Re-run COUNTIF — expect 2.Hint: LibreOffice verified.
- Task 6. Does TRIM fix trailing spaces too?Hint: Yes.
- Task 7. Why not use *P-200* wildcards here?Hint: May over-match other codes.
- Task 8. Clean P-400 and P-200 Milan row.Hint: Same TRIM.
- Task 9. Interview line for this bug.Hint: “COUNTIF missed rows because of leading spaces.”
- Task 10. Use CLEAN+TRIM if non-printables appear.Hint: Paste from web.
- Task 11. Data → Text to Columns can also strip.Hint: Optional.
- Task 12. Bonus: add data validation length = 5 for SKUs.Hint: P-100 shape.
In-browser formula check
Type the fixed formula you would put in the named cell. We check formula text — open the workbook for full recalculation.
Step-by-step solution (try first)
- TRIM all SKUs.
- COUNTIF → 2. LibreOffice: broken 0, fixed 2.
Quick quiz