Spreadsheet Practice

How to standardize yes and no values in Excel

Formula (Excel and Google Sheets)

=IF(ISNUMBER(MATCH(TRIM(A2),{"y","yes","true"},0)),"Yes",IF(ISNUMBER(MATCH(TRIM(A2),{"n","no","false"},0)),"No","Check"))

Match the trimmed value against lists of yes and no spellings: =IF(ISNUMBER(MATCH(TRIM(A2),{"y","yes","true"},0)),"Yes",IF(ISNUMBER(MATCH(TRIM(A2),{"n","no","false"},0)),"No","Check")).

Result on the example
one result per row in B2, filled down to B9
Works in
Excel 2019 and later, including Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in B2, filled down to B9. Change any input and the result updates, or edit the formula to experiment.

How it works

MATCH looks for the value in a short list written into the formula, and it ignores case, so "YES", "Yes" and "yes" all match. TRIM removes the stray spaces in " yes" and "No ". Anything that matches neither list, like "maybe", is flagged with "Check" instead of being guessed.

=IF(ISNUMBER(MATCH(TRIM(A2),{"y","yes","true"},0)),"Yes",IF(ISNUMBER(MATCH(TRIM(A2),{"n","no","false"},0)),"No","Check"))

Result"Yes"

  1. A2"Yes"

    The value in A2.

  2. TRIM(A2)"Yes"

    TRIM: Removes extra spaces, leaving single spaces between words.

    • textA2"Yes"
  3. {"y","yes","true"}{1 row × 3 cols} "y", "yes", "true"

    An array of values.

  4. MATCH(TRIM(A2),{"y","yes","true"},0)2

    MATCH: Position of a value in a list. 0 = exact match.

    • lookup_valueTRIM(A2)"Yes"
    • lookup_array{"y","yes","true"}{1 row × 3 cols} "y", "yes", "true"
    • match_type0
  5. ISNUMBER(MATCH(TRIM(A2),{"y","yes","true"},0))TRUE

    ISNUMBER: TRUE if the value is a number.

    • valueMATCH(TRIM(A2),{"y","yes","true"},0)2
  6. TRIM(A2)"Yes"

    TRIM: Removes extra spaces, leaving single spaces between words.

    • textA2"Yes"
  7. {"n","no","false"}{1 row × 3 cols} "n", "no", "false"

    An array of values.

  8. MATCH(TRIM(A2),{"n","no","false"},0)#N/A

    MATCH: Position of a value in a list. 0 = exact match.

    • lookup_valueTRIM(A2)"Yes"
    • lookup_array{"n","no","false"}{1 row × 3 cols} "n", "no", "false"
    • match_type0
  9. ISNUMBER(MATCH(TRIM(A2),{"n","no","false"},0))FALSE

    ISNUMBER: TRUE if the value is a number.

    • valueMATCH(TRIM(A2),{"n","no","false"},0)#N/A
  10. IF(ISNUMBER(MATCH(TRIM(A2),{"n","no","false"},0)),"No","Check")"Check"not used

    IF: Returns one value if a test is true, another if false.

    • logical_testISNUMBER(MATCH(TRIM(A2),{"n","no","false"},0))FALSE
    • value_if_true"No"not used
    • value_if_false"Check"
  11. IF(ISNUMBER(MATCH(TRIM(A2),{"y","yes","true"},0)),"Yes",IF(ISNUMBER(MATCH(TRIM(A2),{"n","no","false"},0)),"No","Check"))"Yes"

    IF: Returns one value if a test is true, another if false.

    • logical_testISNUMBER(MATCH(TRIM(A2),{"y","yes","true"},0))TRUE
    • value_if_true"Yes"
    • value_if_falseIF(ISNUMBER(MATCH(TRIM(A2),{"n","no","false"},0)),"No","Check")"Check"not used

In Google Sheets

The same formula works in Google Sheets: =IF(ISNUMBER(MATCH(TRIM(A2),{"y","yes","true"},0)),"Yes",IF(ISNUMBER(MATCH(TRIM(A2),{"n","no","false"},0)),"No","Check")). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SWITCH(UPPER(TRIM(A2)),"Y","Yes","YES","Yes","TRUE","Yes","N","No","NO","No","FALSE","No","Check")Yes, Yes, No…SWITCH lists each spelling and its result. Longer, but easy to read.

Common mistakes

  • Blank answers come out as "Check". Add IF(TRIM(A2)="","",…) around the formula if blanks should stay blank.
  • Answers stored as 1 and 0 need "1" and "0" in the lists, and TRIM turns the numbers into text so they match.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Standardize yes and no values

In B2, turn every way of writing yes into "Yes" and every way of writing no into "No", and show "Check" for anything else. Fill down to B9.

Questions

How do I get TRUE and FALSE instead?

Keep just the test: =ISNUMBER(MATCH(TRIM(A2),{"y","yes","true"},0)).

How do I count the yeses after cleaning?

Use =COUNTIF(B2:B9,"Yes").

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.