12

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?

8
  • 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, 2013 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 Sep 27, 2013 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, 2013 at 10:48
  • how is the text copied? Sep 27, 2013 at 10:49
  • I select all the cells, then Ctrl + C.
    – Abbie
    Sep 27, 2013 at 11:03

9 Answers 9

6

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)

IF Statement Example

1
  • 2
    You can omit the "if": =OR(A24,A24="TRUE") will work, or if you're just looking for strings: =A24="TRUE"
    – Simon
    Jun 8, 2017 at 3:33
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/

2

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.

1
  • (1) «if you are ok with all other values then [sic] "TRUE", returning false» seems like a very broad assumption to make. (2) If the user has “TRUE” in Q17 and wants to do IF(Q17,"green","red") somewhere else, how does either of your answers help? (3) Your VBA solution looks a lot like Selkie’s answer, but less clear. (4) Overall, you should provide more / better explanation. Mar 27, 2020 at 19:59
1

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

0

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?

spreadsheet

3
  • Unfortunately, this is not the behavior in Excel. Sep 27, 2013 at 23:14
  • -1 for answering Excel question based on LibreOffice behavior.. thanks for trying to help, but not the same thing.
    – Sam
    Oct 26, 2016 at 14:59
  • I don't know why everyone is puking on about the different behavior. 99 % the same and probably more different between generations of Excell
    – beroe
    Oct 26, 2016 at 17:17
0

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.

0

If you use the TRIM function it will evaluate the target cell's boolean value as TEXT. A2=TRUE (boolean) B2: =IF(TRIM(A2)="TRUE","Y","N") will evaluate it as "TRUE" within the function resulting as "Y".

0

None of these solutions worked well for me (Very long formula resulting in the text), so I got annoyed and wrote my own solution.

Public Function ConvertToBoolean(InputString As String) As Variant
Dim TempResults As Variant

If InputString = "True" Then
    TempResults = True
ElseIf InputString = "False" Then
    TempResults = False
Else
    TempResults = InputString
End If

ConvertToBoolean = TempResults


End Function

This doesn't bother with dealing with 1's and 0's, but you can wrap it around a string and get True, False, or the original string back, depending.

0

I just found a more practical solution to get the cells re-evaluated:

  1. Make sure the column is formatted as General
  2. Mark the column (so you don't accidentally replace something else)
  3. Use CTRL+F to search and replace FALSE with FALSE and then TRUE with TRUE

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.