Excel Challenge 26 : Standardize Yes/No Responses

What You Will Learn

Five survey responses in B2:B6: "y", "No", "YES", "n", "yes" — all valid answers, but inconsistent. You need to normalize them all to YES or NO in column C.

The shortcut: check the first letter of each response. If it is "Y" (after converting to uppercase), the answer is YES. Anything else is NO.

=IF(UPPER(LEFT(B2, 1)) = "Y", "YES", "NO")

Breaking it down:

LEFT(B2, 1) — takes the first character. "y" → "y", "No" → "N", "YES" → "Y".
UPPER(...) — converts it to uppercase so "y" and "Y" are treated the same.
IF(... = "Y", "YES", "NO") — if the first letter is Y, the result is YES. Otherwise, NO.

This works as long as every yes-type answer starts with Y and every no-type answer starts with N. It handles "y", "yes", "YES", "Yes", and even "Yup" the same way.

First letter check normalizes all variants to YES or NO.

The audit formulas:

B9: =COUNTA(A2:A6) — counts total responses.
B10: =COUNTIF(C2:C6,"YES") — counts how many were YES.

Alternative: SWITCH for Explicit Mapping

If you want to list each accepted variant explicitly, SWITCH gives you full control:

=SWITCH(UPPER(B2), "Y", "YES", "YES", "YES", "N", "NO", "NO", "NO")

This maps each specific input to its output. It is more work to set up but gives you control if some answers should not be treated as YES/NO.

Alternative: Lookup Table

If the list of possible answers might grow, a mapping table keeps normalization logic outside the formula:

=VLOOKUP(UPPER(B2), $F$2:$G$10, 2, FALSE)

Add new variants to the table instead of editing formulas.

The first-letter shortcut works here but has limits. If a response starts with a blank space, LEFT returns the space instead of Y or N. Clean the data with TRIM first if that happens.

The UPPER function makes the check case-insensitive. Without it, "yes" and "YES" would match but "Yes" and "y" would not — a lowercase "y" would not equal uppercase "Y". By converting everything to uppercase before comparing, every variant of Y and N produces the same result. The same trick works for any text normalization where case should not matter.