# 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 ```