How do I make Excel recognise TRUE or FALSE text as boolean values?

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?

Solution 1:

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(OR(A24,A24="TRUE"),TRUE,FALSE)

Solution 2:

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.

http://dailydoseofexcel.com/archives/2009/10/12/converting-cells-formatted-as-text/

Solution 3:

People tend to make boolean values harder then they are. This is the best way to handle your problem in basic excel in my opinion (if you are OK with all other values then "TRUE" returning false):

=UPPER(TRIM(A1))="TRUE"

In VBA :

Public Function ConvertToBoolean(InputString As String) As Variant

        Dim TempText As String
        TempText = UCase(Trim(InputString))

        If TempText = "TRUE" Or TempText = "FALSE" Then
            ConvertToBoolean = TempText = "TRUE"
        Else
            ConvertToBoolean = InputString
        End If

End Function

It deals with white-spaces ,upper/lowercase and other values.

Solution 4:

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