Hi
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
“Yes”;;“No”
Result is “Yes” for positive values, “No” for zeroes, nothing for negatives.
Updated
Maybe in your language the separator is the comma not the semicolon.
Please see the attached file: YesNoFormat.ods
1 Like
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 https://ask.libreoffice.org/c/spanish/10
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:
[GREEN][=1]"✓";[RED][=0]"✗";[BLUE]General;[BLUE]@
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!
1 Like
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.