- 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
. See example:**OverPunch Code** **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