# How to standardize yes and no values in Excel

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")).

**Excel:** `=IF(ISNUMBER(MATCH(TRIM(A2),{"y","yes","true"},0)),"Yes",IF(ISNUMBER(MATCH(TRIM(A2),{"n","no","false"},0)),"No","Check"))` (Excel 2019 and later, including Microsoft 365)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B |
| --- | --- |
| Opted in (as entered) | Standard |
| Yes |  |
| y |  |
| NO |  |
| yes |  |
| n |  |
| TRUE |  |
| maybe |  |
| No |  |

Formula in B2, filled to B9: `=IF(ISNUMBER(MATCH(TRIM(A2),{"y","yes","true"},0)),"Yes",IF(ISNUMBER(MATCH(TRIM(A2),{"n","no","false"},0)),"No","Check"))`

Result: Yes; Yes; No; Yes; No; Yes; Check; No

## 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.

## Other ways

- `=SWITCH(UPPER(TRIM(A2)),"Y","Yes","YES","Yes","TRUE","Yes","N","No","NO","No","FALSE","No","Check")`: 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.

## 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")`.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/standardize-yes-and-no-values