Change Boolean to Yes/No instead of True/False


How can Calc interpret or format cells that have “Yes” or “No” word strings to 1 or 0 number values?

(Really using Spanish Si and No.)

Use as number format


Result is “Yes” for positive values, “No” for zeroes, nothing for negatives.


Maybe in your language the separator is the comma not the semicolon.

Please see the attached file: YesNoFormat.ods

Sorry, first time formatting a user-defined cell.
What do I type under “Format code”?
Typed “Yes”;;“No” but nothing happens.

Please attach minimal sample file about what you want achieve. If you like there is a site in Spain

The format code separator is always ; semicolon, language independent.

i suggest basically the same as m.a.riosv wrote, but a little better:

in format cells dialogue fill format code input with the following:

[=1]"Yes";[=0]"No";General - this means it will be yes on true/1 values, no on false/0 values and will use general format otherwise.

one may even use the following string to format it as / instead of yes/no and color differentiation:

image description

this works on libreoffice calc v6.1.5.2 for ubuntu - i’ve tested

i call it “a little bit better” solution because it implements design by contract pattern, i.e. if for some unexpected reason the value will be something else than a boolean, then it will be displayed as usual, and not hidden, thus hiding a possible error in design!

Caveat, negative values are displayed without minus sign then (which may be neglect-able though but confusing). And no, if negative values are desired AFAIK there’s no format code that would cover all possibilities as there are only 3 subformats (plus the text subformat).

It occurred to me that the question was not about formatting but actually literally about interpreting a Yes or No word, or Si and No. So that would be the formula

=IF( A1="Si"; 1; IF( A1="No"; 0; A1))

assuming content in cell A1, which if that is Si displays 1, if No displays 0, else the content of A1, or 0 if A1 is empty.