OverPunch Code - Custom Function

This is the longest formula I ever built (and I hope it stays that way). My customer had a need to convert numbers from a negative taking 9 bytes to a number taking 8 bytes. We did this by taking a number like -5468 and changing to 546A. He called this an OverPunch Code. See example:
Original Number With OverPunch










Look at the number in cell B19. If it is a negative number, make it absolute or positive, convert it to text, grab the rightmost digit of the number, take that digit and use a lookup table in ‘LookUpPlace’ to find a converter and tack that on the end of the new number.
{ Sorry! I can no longer FIND the Personal Add-Ins. After I did all this, I decided it was too long to re-type every month and converted it to a user-defined function that could reside in his PERSONAL.XLA. }

The second function is actually the first one I wrote. Later I wrote newPunch to use a self-contained array.

Function newPunch(TheNumber)
     Dim ChoArray
          ChoArray = "ABCDEFGHI}"       'Build array to choose the suffix
     If TheNumber < 0 Then
          newPuch = Application.Text(Abs(Left(TheNumber, Len(Abs(TheNumber - 1)))) _
          & Mid(ChoArray, Right(TheNumber, 1)), "0")
     End If
End Function

Function OverPunch(TheNumber, ConverArray)
    'Show the number and where the series of the array is located
        'The ConverArray may be the address of array cells 
                  '(i.e. A1:B10 or a defined name "LookUp")
    If TheNumber < 0 Then         'The number or cell address
      OverPunch = Application.Text(Abs(Left(TheNumber, Len(Abs(TheNumber - 1)))) _
        & Application.VLookup(Val(Right(TheNumber, 1)), ConverArray, 2, False), "0")
     OverPunch = TheNumber
   End If
End Function


Alan's Home Falkland Islands stamps Excel & VBA Family Tree