How to convert Boolean true/false to number (1/0) or text in Excel?
Sometimes, excel formulas return the Boolean values (TRUE or FALSE) in Excel. And you can edit the original formula to convert the Boolean values (TRUE or FALSE) to number (1 or 0) or text in Excel easily.
- Convert Boolean values (TRUE or FALSE) to number (1 or 0) in Excel
- Convert Boolean values (TRUE or FALSE) to text in Excel
- More articles about converting between numbers and text...
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
In this section, I will introduce three methods to convert Boolean values (TRUE or FALSE) to number (1 or 0) in Excel.
Add -- before original formula
Change original formula to =--(original_formula), and the Boolean values will be converted to number 1 or 0 automatically. For example, the original formula is =B2>C2, you can change it to =--(B2>C2).
Combine INT function and original formula
Combine the INT function and original formula as =INT(original_formula), and the Boolean values will be converted to number 1 or 0 automatically. Let’s say the original formula is =B2>C2, and you can convert it to =INT(B2>C2).
Multiply original formula by 1
You can multiply the return Boolean values (TRUE or FALSE) by 1, and then the TRUE will change to 1, and FALSE to 0. Assuming the original formula is =B2>C2, you can change it to =(B2>C2)*1.
Note: You can also divide original formula by 1 or add 0 to original formula to change the return TRUE to 1 and FALSE to 0. Follow above example, change the original formula to =(B2>C2)/1 or =(B2>C2)+0.
If you want to convert the Boolean values (TRUE or FALSE) to certain text, says Yes or No, you can change the formula as below:
For example, the original formula is =B2>C2, you can change the formula to =IF(B2>C2,"Yes","NO"). This new formula will change TRUE to Yes, and change FALSE to No. See screenshot:
Note: In the new formula, Yes and No are the specified texts you will change the Boolean values to, and you can change them to any texts as you need.