Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have been using the INT formula to convert boolean values TRUE and FALSE into 1s and 0s. However, I have copied some data I have been given into my spreadsheet and Excel hasn't recognised the text as boolean in some cases.

The TRUE and FALSE text is aligned left and is formatted as 'General'. The INT formula doesn't recognise this as boolean. However, as soon as I click a TRUE or FALSE cell and then hit enter, the text becomes center aligned and my formula recognises it as boolean and converts it to an integer.

Is there any way I can make Excel recognise this without having to click on every cell and hit enter?

share|improve this question
I've found the workaround to include =IF(A1,TRUE(),FALSE()) within my INT formula, but would be interested to hear if anyone has another idea. – Abbie Sep 27 '13 at 10:31
how are you copying the data? Do you need to click them for them to be recognized or could you just force recalculation on the sheet and everything would update? You can do this in the formula tab of the ribbon or ctrl alt f9 – Raystafarian Sep 27 '13 at 10:39
I have tried 'Calculate Now' and Ctrl Alt F in the original sheet as you suggested but this doesn't seem to change the T/F text. – Abbie Sep 27 '13 at 10:48
how is the text copied? – Raystafarian Sep 27 '13 at 10:49
I select all the cells, then Ctrl + C. – Abbie Sep 27 '13 at 11:03

What does =TYPE(Value) show for the column of TRUE/FALSE?

I am using LibreOffice and not "true" Excel, but it recognizes even text fields properly...

Maybe you could search/replace TRUE with =TRUE?


share|improve this answer
Unfortunately, this is not the behavior in Excel. – Excel Tactics Sep 27 '13 at 23:14

You may have pasted data that included embedded spaces.

"true" and "false" will automatically convert to TRUE and FALSE "true " and "false " will be treated like text.

share|improve this answer

Old question - but the following method may be handy for posterity.

Excel is recognizing the booleans as text. You can change the format to General for the entire column, but Excel won't re-evaluate the format until you click in each cell and hit Enter. An alternative is to use the text-to-columns function and e.g. use a very wide fixed width. The outcome will be the same single column, but Excel will be forced to re-evaluate the format and update all of your true and false entries to booleans.

share|improve this answer

As you anyway convert your logical values to number why do you want to change your data instead of adjusting your formula to accept text too: =INT(IF(ISLOGICAL(A1),A1,A1="TRUE"))

share|improve this answer

Excel won't recognize text "TRUE" or "FALSE" as their boolean equivalents... If you need to convert them, an IF statement will accomplish the task:


IF Statement Example

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .