Excel Power Utility Pak

I first found out about John Walkenback from his books. He, without question, has the best books on Excel. Back when books only were made from dead trees I judged books by how much real information versus fluff. He wins outright. His books now are available in paper format or electronically.

He also has a site with many good Excel tips.

For many years I have also used his add-in. It is another reason why I am running Excel on Windows as the Mac does not support these add-ins.

In fact even if it had only the cells items

And the text tools items

It is well worth the money!

I also get the source code which he sells for an additional price. That opens up many opportunities for programming or learning programming.

On his site, and I agree he says:

PUP is an Excel add-in that adds dozens of new features and functions to Excel. A free 30-day trial version is available.

There is also an Enhanced Data Form for free.

The J-Walk Enhanced Data Form is a free Excel add-in that provides a general-purpose data entry dialog box. The VBA code is available for a small fee.

There are other competing Excel add-ins. But I trust J-Walk’s and it is just the right amount of add-ins in the menu bar.

If you ever have questions or issues with Pup, John replies very quickly to any support request.

I had a glitch installing the latest version of Pup, enabling the worksheet functions, and found out that it was my Excel file.

  • You need to ensure that a workbook’s VB project is not protected. If you unprotect your VB project, it should work.
  • And, you also need to make sure the workbook is not shared.

Hyphen Minus Dash

I was working in Excel matching some strings and they were not matching even though they looked the same on the screen.

Well it turns out that there are different kinds of dashes!

A normal dash you use is the keyboard is the minus sign on your keyboard. But there are others that can get put in. In my case there were some normal minus characters but there were also some actual dash characters.

The difference as described in the link above is the familiar “-”

Ascii hyphen, with multiple usage, or “ambiguous semantic value”; the width should be “average”.

But somehow in the data there was also this character, “‐” which is

unambiguously a hyphen character, as in “left-to-right”; narrow width.

It took some digging to figure this out, looking at the boolean values in the text to see what characters were there.

So in order to spare me future dash grief, I put together the following Excel function. It’s self explanatory.

Note that it also deletes spaces as I don’t want any leading or trailing ones in the strings I’m dealing with in Excel. You could comment those lines out of course.

Function tagfix(ss As String) As String
 
Dim Counter As Integer
Dim s As String
 
tagfix = ""
 
For Counter = 1 To Len(ss)
   
    s = Mid(ss, Counter, 1)
   
    Select Case s
        ' Comment out the following line
        ' if you don't want spaces removed
        Case " "
          ' skip spaces
          ' do nothing
        Case 0 To 9
          tagfix = tagfix & s
        Case "a" To "z"
          tagfix = tagfix & UCase(s)
        Case "A" To "Z"
          tagfix = tagfix & s
        Case Else
          tagfix = tagfix & "-"
        End Select
 
Next

Wait there’s more! If you setup the following subroutine you can run the fix on a selection.

Sub fixtag()
 
Dim c As Range
 
For Each c In Selection
    c = tagfix(c.Value)
Next c
 
End Sub

Excel Parser

I had written my own but it had a bug. Then I found this code below on this site.

It gives credit to John Walkenbach who writes, bar none, the best books on Excel.

Public Function ExtractElement(Txt, n, Separator) As String
 
'   Returns the nth element of a text string, where the
'   elements are separated by a specified separator character 
 
    Dim Txt1 As String, temperament As String
    Dim ElementCount As Integer, i As Integer
 
    Txt1 = Txt

'   If space separator, remove excess spaces
    If Separator = Chr(32) Then Txt1 = Application.Trim(Txt1)
 
'   Add a separator to the end of the string
    If Right(Txt1, Len(Txt1)) <> Separator Then _
        Txt1 = Txt1 & Separator
 
'   Initialize
    ElementCount = 0
    TempElement = ""
 
'   Extract each element
    For i = 1 To Len(Txt1)
        If Mid(Txt1, i, 1) = Separator Then
            ElementCount = ElementCount + 1
            If ElementCount = n Then
'               Found it, so exit
                ExtractElement = TempElement
                Exit Function
            Else
                TempElement = ""
            End If
        Else
            TempElement = TempElement & Mid(Txt1, i, 1)
        End If
    Next i
    ExtractElement = ""
End Function

And the following function returns nothing more than the number of elements you get when you cut a string according to a particular delimiter.

Public Function ExtractElementCount(Txt, Separator) As String
'   Returns the number of different elements in a string
'   separated by a specified separator character
'   This is useful if you are, fex, trying to grab the last element or the second to last.
 
    Dim Txt1 As String, temperament As String
    Dim ElementCount As Integer, i As Integer
 
    Txt1 = Txt
 
'   If space separator, remove excess spaces
    If Separator = Chr(32) Then Txt1 = Application.Trim(Txt1)
 
'   Add a separator to the end of the string
    If Right(Txt1, Len(Txt1)) <> Separator Then _
        Txt1 = Txt1 & Separator
 
'   Initialize
    ElementCount = 0
    TempElement = ""
 
'   Extract each element
    For i = 1 To Len(Txt1)
        If Mid(Txt1, i, 1) = Separator Then
            ElementCount = ElementCount + 1
        End If
    Next i
    ExtractElementCount = ElementCount
End Function

Footnote

I see that John Walkenbach has a post about The Versatile Split Function

VBA’s Split function, introduced with Excel 2000, can simplify many programming tasks. This function accepts a text string, and returns a zero-based variant array that contains the elements of the string (you specify the character that delimits the elements).

And then you can replace the ExtractElement with just this! Wow!

Function ExtractElement(str As String, n As Integer, sepChar As String) As Variant
'   Returns the nth element from a string,
'   using a specified separator character
    Dim x As Variant
    x = Split(str, sepChar)
    If n > 0 And n - 1 < = UBound(x) Then
       ExtractElement = x(n - 1)
    Else
        ExtractElement = ""
    End If
End Function

And in case you haven't had enough you can just do a word count with this.

Function WordCount(txt as String) As Long
'   Returns the number of words in a string
    Dim x As Variant
    txt = Application.Trim(txt)
    x = Split(txt, " ")
    WordCount = UBound(x) + 1
End Function

There are some more examples on the j-walk site.

  • extract a path or a filename from a full filespec
  • Counting specific characters in a string
  • Finding the longest word

Excel XOR

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.

A B Result
1 1 0
1 0 1
0 1 1
0 0 0

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.

Trim Function

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.