Revision as of 17:47, 13 August 2010
Base Ten To Base Letter
This is a pair of related functions; The first converts a base 10 number into a "base letter" value, following the structure we've all seen in the column headings in Excel. So, the base 10 value 5 converts to "E", and 29 converts to "AC". The second function converts a "base letter" value to base 10.
The original intent of these functions was to generate column names within Excel, or figure out how many columns over a given column was. This was before I discovered the built-in Offset functionality in Excel. I've recently found a new use for the functions - I use them to create an auto-generated "Sample ID" string for a database that tracks samples received for testing, using the format "11-25-10-A", "11-25-10-B", etc. The functions take up the task of "counting by letters" and make it much easier.
As with all User-Defined functions, you should create a new code module (with a name that is NOT already being used by one of the functions), and copy/paste the following code into the module.
I've included detailed internal comments to explain how the conversion process works.
' Code courtesy of UtterAccess Wiki
' Original submission by Dennis Kuhn (aka doctor9)
' on 8/13/10
' This code is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided this copyright notice is left unchanged.
Public Function Base10ToBaseLetter(lngNumber As Long) As String
' In concept, it works like this:
' If you've got a number like 156, you'd parse it out as:
' (1) hundreds, (5) tens, and (6) ones.
' This function basically does the same thing, only working down the powers of 26.
' For example, 69 in base 10 is converted to Base Letter like this:
' 2 x 26^1 plus 17 x 26^0. Using letters to represent the 2 & 17, we get "BQ".
Dim intCounter As Integer, strTemp As String
strTemp = ""
' Skip down thru powers of 26 until we find a value less than the argument value
For intCounter = 6 To 0 Step -1
' Once we've started extracting value from the argument, force further
' extractions from that point on, even zero values.
If lngNumber > 26 ^ intCounter Or Len(strTemp) > 0 Then
' Add the base-26 value to the string...
strTemp = strTemp & Chr$(64 + Int(lngNumber / (26 ^ intCounter)))
' Subtract the equivalent demical value from the number
lngNumber = _
lngNumber - ((26 ^ intCounter) * (Int(lngNumber / (26 ^ intCounter))))
' A little cleanup next. Base Letter doesn't have a "zero" equivalent, so when
' a capital "A" (the equivalent of 1) is reduced by 1, it becomes an "@" symbol.
' We need to convert "@" to "Z", and then reduce the next significant "digit" by one
' to accomodate this.
' Cascade carried-over values
While InStr(strTemp, "@") > 0
' Starting on the left, make the value just left of the "@" one step lower,
' then convert the "@" to a "Z"
strTemp = Trim(Left(strTemp, InStr(strTemp, "@") - 2) & _
Chr$(Asc(Mid(strTemp, InStr(strTemp, "@") - 1, 1)) - 1) & "Z" & _
Mid(strTemp, InStr(strTemp, "@") + 1, 7))
' Remove leading "@" character if necessary
While Left(strTemp, 1) = "@"
strTemp = Trim(Mid(strTemp, 2, 7))
' Return the final string value
Base10ToBaseLetter = strTemp
Public Function BaseLetterToBase10(strInput As String) As Long
' Move through the string, converting each letter to a number value by multiplying
' the letter position (A=1, B=2, etc.) by the power of 26 indicated by it's position
' in the string.
Dim intChar As Integer
If Len(strInput & "") > 0 Then
BaseLetterToBase10 = 0
For intChar = 1 To Len(strInput)
BaseLetterToBase10 = BaseLetterToBase10 + _
(Asc(Mid(strInput, intChar, 1)) - 64) * 26 ^ (Len(strInput) - intChar)
Let's say you know the Base Letter value, and want to auto-increment it by one. You could get the next value with a simple function that calls these two functions:
Function NextLetters(strInput As String) As String
Dim lngTemp As Long
lngTemp = BaseLetterToBase10(strInput)
lngTemp = lngTemp + 1
NextLetters = Base10ToBaseLetter(lngTemp)
Dennis Kuhn doctor9 at UtterAccess.com