Are you looking for a method to convert Yes and No to True and False in Excel? This blog post explains you to convert Yes/No values to True/False.
No direct formula to convert all Yes and No values in Excel. It will be very useful for your analysis if the data is stored in True and False instead of Yes and No.
Convert using Equal To operator
Select the cell C2 and Type the formula =B2=”Yes”. Update the cell reference for all other cells to apply the same formula.
IF Function
You can use the Excel if function in the formula to convert Yes and No to True or False.
Enter the formula =IF(B2=”Yes”, TRUE, FALSE) in cell C2. Update the cell reference for all other
cells to apply the same formula.
Convert using SUBSTITUTE Function
You can convert Yes and No using the Excel string function called Substitute.
Select the cell D2 and Type the formula =SUBSTITUTE(SUBSTITUTE(B2, “Yes”, TRUE), “No”, FALSE). Update the cell reference for all other cells to apply the same formula.
Convert using Excel Flash Fill
It automatically fills the values based on the patterns you entered in the above cells.
Start typing in cell E2, and type True and False for Yes and No. when you start typing cell E3, Excel suggests the pattern, you just enter it will automatically fill the rest of the cells.
Convert Yes and No To True and False using Excel VBA
Sub YesNoToTrueFalse()
Dim rg As Range
For Each rg In Selection
If rg.Value = "Yes" Then
rg.Offset(0, 4).Value = True
Else
rg.Offset(0, 4).Value = False
End If
Next
End Sub
You can copy and paste the above script into your VBA Module. You have used the for loop with a conditional statement nested inside of it. It is used to iterate through an array of selected cells. and check each element in the range. If the value is equal to “Yes” then set true to the cell in column E. Otherwise, set to false.
Conclusion
You learned 5 different methods to convert Yes and No values to True and False in Excel. Flash fill is very simple to use. Function methods are automatically updating the values whenever the data get updated.
If you have any questions, please leave a comment below and I will get back to you as soon as possible.
