How to get the Character Sequence for a number? (1 Viewer)

prabha_friend

Prabhakaran Karuppaih
Local time
Today, 14:33
Joined
Mar 22, 2009
Messages
771
How to get the Character Sequence for a number? (Any Number in this Case)

For Example:
1 = A
26 = Z
53 = AA
78 = AZ
79 = BA

Like that for any Number? May be even a 1000000

How to do it?

I suspect that there is a clue is present in the following post:
https://access-programmers.co.uk/forums/showthread.php?t=294695

But don't know How to use it? :banghead:

Code:
excel.WorksheetFunction.Log(26,676)
returns 2
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:03
Joined
Jan 20, 2009
Messages
12,849
Maybe this code at UtterAccess is what you want?

It won't get the same results you have given as examples but they don't make sense anyway.

BTW I wrote that code based on a concept by another one of their members. Although they licence it under Creative Commons and insist it is attributed to them they removed the credits the original author and I had included.
 

ashleedawg

"Here for a good time"
Local time
Today, 02:03
Joined
Jun 22, 2017
Messages
154
Here's another one, similar idea, originally spawned from the formula to convert from Red/Green/Blue (0-255) values to Access-friendly decimal (Long) values: RGB = (R * 16 ^ 0) + (G * 16 ^ 2) + (B * 16 ^ 4) and vice versa.

However I soon realized that it's not terribly efficient; the example mentioned above lets you store up to 6 A-Z characters, and this one isn't much better, 10 A-Z characters, using up to 15 numeric digits to do so, or a string of up to 6 upper/lower/symbol characters.

I'm curious if there's a use for this that I haven't thought about?


Code:
'Converts Strings to Numbers and back.
'(uses functions Fmod & Fix to allow use of Double data type)

'  10 x 26 characters:
'    Use constants below for strings up to 10 characters in length using A to Z CAPS ONLY:
Const CharStart = "A"
Const CharStop = "Z"

'  6 x 63 characters:
'    Use constants below for strings up to 7 characters in length using characters:
'    ?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~
'Const CharStart = "?"
'Const CharStop = "~"


Public CharMin As Byte, CharMax As Byte
Public expFactor As Double, chrOffset As Double

Sub SetRange()
expFactor = Asc(CharStop) - Asc(CharStart) + 2
chrOffset = Asc(CharStart) - 1
End Sub

Function FMod(a As Double, b As Double) As Double
'replaces MOD function so that it works with Double
'https://stackoverflow.com/questions/23549947/mod-with-doubles
FMod = a - Fix(a / b) * b
If FMod >= -2 ^ -52 And FMod <= 2 ^ -52 Then '+/- 2.22E-16
    FMod = 0
End If
End Function

Function convert_String_to_Number(numIn As String) As Double
Dim ret As Double, x As Integer
SetRange
For x = 1 To Len(numIn)
    If Asc(Mid(numIn, x, 1)) < Asc(CharStart) _
        Or Asc(Mid(numIn, x, 1)) > Asc(CharStop) Then _
        MsgBox "Invalid Character: " & Mid(numIn, x, 1)

    ret = ret + ((Asc(Mid(numIn, x, 1)) - chrOffset) * expFactor ^ (Len(numIn) - x))
Next x
convert_String_to_Number = ret
End Function

Function convert_Number_to_String(decIn As Double) As String
Dim x As Integer, y As Integer
Dim numChars As Byte, d(25) As Double, ret As String
SetRange
numChars = (Int(Log(decIn) / Log(expFactor))) + 1
For x = numChars To 1 Step -1
    d(x) = decIn
    For y = 1 To numChars - x
         d(x) = Fix(d(x) / expFactor)
    Next y
    d(x) = FMod(d(x), expFactor)
    ret = Chr(chrOffset + d(x)) + ret
Next x
convert_Number_to_String = ret
End Function

Here's an interesting string-compression method from Codeguru (post #5).
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:03
Joined
Feb 28, 2001
Messages
26,996
It will take some coding then, and at the moment I have to limit my answers. Wifey still can't do some things after surgery (still in therapy) and therefore I'm still booked up with being the "gofer" and "chauffer."
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 20:03
Joined
Jan 20, 2009
Messages
12,849
It will take some coding then

I'm pretty sure the code I contributed at utteraccess is exactly what they need. It has the same results as Excel column designators but goes a lot further.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 04:03
Joined
Feb 28, 2001
Messages
26,996
Wouldn't doubt it for a moment, Greg. But sometimes I have to rush my way through some of these answers because (like today as an example) I have to drive a family member to her doctor's appointment because wifey can't manipulate her mother's wheelchair by herself.
 

ashleedawg

"Here for a good time"
Local time
Today, 02:03
Joined
Jun 22, 2017
Messages
154
Unfortunately, it is limited only upto to column number 16384 (XFD)

It's Excel that's limited to column XFD, not the code.

I'm curious, what exactly do you need to accomplish with character sequences?
 

Users who are viewing this thread

Top Bottom