Content
Resources
To Do
Toolbox

 Base Ten to Base Letter
(Difference between revisions)
 Revision as of 17:46, 13 August 2010Doctor9 (Talk | contribs) (Two functions that convert Base 10 numbers to letters similar to Excel column names, and back again.)← Previous diff Revision as of 17:47, 13 August 2010Doctor9 (Talk | contribs) Next diff → Line 40: Line 40: strTemp = strTemp & Chr\$(64 + Int(lngNumber / (26 ^ intCounter))) strTemp = strTemp & Chr\$(64 + Int(lngNumber / (26 ^ intCounter))) ' Subtract the equivalent demical value from the number ' Subtract the equivalent demical value from the number - lngNumber = lngNumber - ((26 ^ intCounter) * (Int(lngNumber / (26 ^ intCounter)))) + lngNumber = _ + lngNumber - ((26 ^ intCounter) * (Int(lngNumber / (26 ^ intCounter)))) End If End If Next intCounter Next intCounter

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
'   Code courtesy of UtterAccess Wiki
'   http://www.utteraccess.com/wiki/index.php/Category:FunctionLibrary
'   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))))
End If
Next 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.

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))
Wend
Wend

'   Return the final string value
Base10ToBaseLetter = strTemp

End Function

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)
Next intChar
End If

End Function

Sample usage:

CODE
? Base10ToBaseLetter(704)
AAB

? BaseLetterToBase10("AAB")
704

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:

CODE
Function NextLetters(strInput As String) As String

Dim lngTemp As Long

lngTemp = BaseLetterToBase10(strInput)
lngTemp = lngTemp + 1
NextLetters = Base10ToBaseLetter(lngTemp)

End Function

Dennis Kuhn doctor9 at UtterAccess.com