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
-106.68	106.6H
65.00	65.00
-215.55	215.5E
-27006	2700F
-32490	3249}

=IF(B19<0,TEXT(ABS(LEFT(B19,LEN(ABS(B19-1))))&VLOOKUP(VALUE(RIGHT(B19,1)),LookUpPlace,2,FALSE),"0"),B19)

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")
    Else
     OverPunch = TheNumber
        
   End If
     
End Function