Excel converts boolean 0 and 1 to FALSE and TRUE (1 Viewer)

Libre

been around a little
Local time
Today, 07:40
Joined
May 3, 2007
Messages
660
Maybe this should be in the Excel section, but it also has to do with Access so I'm posting it here. When I have a datasheet in Access that has Boolean true/false values it reports them as 1 and 0, respectively. When I copy this data and paste it into Excel, instead of 1 or 0, it converts it to TRUE or FALSE. This is causing some problems. I tried reformatting the datatype in Excel but nothing I do seems to change it. Hoping for a quick/easy fix if there is one. Thanks for your help.
 

llkhoutx

Registered User.
Local time
Today, 09:40
Joined
Feb 26, 2001
Messages
4,018
Use a conditional if to change the true Fales values to -1 0.
 

Libre

been around a little
Local time
Today, 07:40
Joined
May 3, 2007
Messages
660
Not that my post sparked much interest, (and thank you llkhoutx but your answer was too sparse for me to make any use of it - also I did not want to add any columns to the existing spreadsheet, also I've seen conditionals fail when the condition is based on text saying TRUE or FALSE because the conditional is also using TRUE and FALSE as Boolean values but not as literals and it gets confused), but I solved it on my own. This is a bug or error(?) that has plagued me for years and the solution is quite simple.
The following Macro in Excel will change all TRUE to 1 and all FALSE to 0 from columns E to EF:

Code:
 Sub Macro1()
'
' Macro1 Macro
' Change FALSE to 0 and TRUE to 1
'
' Keyboard Shortcut: Ctrl+Shift+F
'
    Columns("E:EF").Select
    Selection.Replace What:="FALSE", Replacement:="0", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("E:EF").Select
    Selection.Replace What:="TRUE", Replacement:="1", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
 End Sub
 

Users who are viewing this thread

Top Bottom