Category: Excel

Excel 2010 doesn’t have an XOR function. The newer versions do. I have needed this form of logic a couple of times recently and I found a good tip here.

XOR works like this.

So, if you don’t have an XOR function you can write a long version of the XOR as follows

=OR(AND(NOT(A),B),AND(A,NOT(B)))

This is how the XOR operation is defined if you study boolean logic. I did 25 years ago.

But there is a clever way to do it in Excel.

The web page lined above noted that the equivalent can be done from the observation that XOR is nothing but <> (the not equal to sign).

So, instead of going crazy with brackets writing the lengthy formula above. you can just use

=A<>B

One aside that I found out. You need to be careful with your brackets. I found out that

This

 =IF(B4=""<>I4<>"","",1)

Is not the same as this

=IF((E15="")<>(L15<>""),"",1)

Hint: Use the second form with the extra brackets.

This is to remove excess characters from a string. It can be enhanced with options or changed depending if you want spaces or not etc.

Function fixchar(s As Variant) As Variant
 
Dim a, b, i As Integer
    fixchar = ""
   
    
    If Len(s) = 0 Then Exit Function
     
    For i = 1 To Len(s)
        b = Mid(s, i, 1)
       
        
        ' No Spaces: "[A-Z,a-z,0-9]"
        ' Spaces OK: "[A-Z,a-z,0-9 ]"
       
        If b Like "[:,/,A-Z,a-z,0-9 ]" Then
            fixchar = fixchar & b
        End If
    Next i

End Function

There is a more sophisticated function here where you can just run a macro on a selection instead of using a function as above.

I’m always forgetting the syntax and also here’s some tricks.

1. How to highlight cells based on another cell’s value in Excel 2011 – Ask Different
2. Format cell color based on value in another sheet and cell – Stack Overflow
3. Conditional format cells based on match in another sheet – Stack Overflow

How to VLOOKUP with Multiple Criteria Using INDEX and MATCH in Excel

Directory Tree Builder Add-In for Excel.

DirTree will create a tree-like hierarchical list of all the folders and (optionally) files contained within a specified directory. When you load the Add-In, an item with the caption Build Directory Tree is created on your Tools menu. When you click that item, the Directory Tree Configuration screen, described below, will appear. You can set the various options of the tree from the configuration screen.

Function revstr(s As String) As String 
'
' revstr Macro
'

revstr = ""
 
If s = "" Then Exit Function
 
For j = Len(s) To 1 Step -1   
    revstr = revstr & Mid(s, j, 1)
Next

End Function