Ask Your Question

Change Boolean to Yes/No instead of True/False

asked 2016-05-31 23:36:20 +0200

ventolinmono gravatar image

updated 2016-05-31 23:39:52 +0200


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

edit retag flag offensive close merge delete

3 Answers

Sort by » oldest newest most voted

answered 2019-05-14 21:07:21 +0200

librelive gravatar image

updated 2019-05-15 22:23:32 +0200

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: [GREEN][=1]"✓";[RED][=0]"✗";[BLUE]General;[BLUE]@

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!

edit flag offensive delete link more


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

erAck gravatar imageerAck ( 2019-05-14 22:18:48 +0200 )edit

answered 2019-05-14 22:37:20 +0200

erAck gravatar image

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.

edit flag offensive delete link more

answered 2016-05-31 23:58:38 +0200

m.a.riosv gravatar image

updated 2016-06-01 02:24:21 +0200

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

edit flag offensive delete link more


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

ventolinmono gravatar imageventolinmono ( 2016-06-01 01:49:28 +0200 )edit

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

m.a.riosv gravatar imagem.a.riosv ( 2016-06-04 11:39:47 +0200 )edit

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

erAck gravatar imageerAck ( 2019-05-14 22:19:43 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-05-31 23:36:20 +0200

Seen: 1,865 times

Last updated: May 15