Get Letter from Number

[alketrazz]

Hi

I have some code that gets the letter to the corresponding number

Function GetLetter(lNum As Long) As String
  If lNum <= 26 Then
    GetLetter = Chr(lNum + 64)
  Else
    GetLetter = Chr(Int((lNum - 1) / 26) + 64) & Chr(((lNum - 1) Mod 26) + 65)
  End If
End Function

I'm trying to do a similar thing but not with the alphabet, only with a string of letters in the alphabet.

My string is "ABCDFGHIJKMQRSUVWXYZ" 20 characters.

if the number is 22 this will return "AB" and 42 will return "BB" which is also correct.

However after ZZ it goes wrong. of rexample if the number is 522 the it returns "BB"

Hope this makes sense, here's my code so far.

Function GetAvailableLetter(iNum As Long) As String


  Dim iCount As Long, iMax As Long, iPos As Long, strSearch As String
  
  strSearch = "ABCDFGHIJKMQRSUVWXYZ"
  iMax = Len(strSearch)
    
  If iNum > iMax Then
    iPos = Int((iNum - 1) / iMax)
    If iPos > iMax Then
      iPos = iNum Mod iMax
      GetAvailableLetter = GetAvailableLetter & Mid(strSearch, iPos, 1)
    End If
  End If
  
  iPos = iNum Mod iMax
  iPos = IIf(iPos = 0, iMax, iPos)
  GetAvailableLetter = GetAvailableLetter & Mid(strSearch, iPos, 1)
  
End Function

 

[MickG]

I'll get my Coat !!!!!

 

[shg]

To convert from string back to number,

Function ToNum(sCol As String, sSym As String) As Long
    ' shg 2014
    
    If Len(sCol) Then ToNum = InStr(1, sSym, Right(sCol, 1), vbTextCompare) _
       + Len(sSym) * ToNum(Left(sCol, Len(sCol) - 1), sSym)
End Function

E.g., =ToNum("ARMAUJBH", "ABCDFGHIJKMQRSUVWXYZ") returns 2,147,483,647

 

[VBA Geek]

Check out the two progressions from 400 to 401 and from 420 to 421 using your function.

Good catch, Rick

I think I might be getting the right progression now:

Function dhStrNum(lNumber As Integer) As String
Dim intCK    As Variant
Dim aStr     As Variant
Dim iInt     As Integer
Dim lStr     As Integer
Dim strRes   As String
Dim iMod     As Integer
Dim cnt      As Integer
aStr = Array("A", "B", "C", "D", "F", "G", "H", "I", "J", "K", "M", "Q", "R", "S", "U", "V", "W", "X", "Y", "Z")
lStr = UBound(aStr) + 1
iMod = lNumber Mod lStr
If iMod > 0 Then
    strRes = aStr(iMod - 1)
    lNumber = lNumber - iMod
Else
    strRes = aStr(UBound(aStr))
    lNumber = lNumber - (UBound(aStr) + 1)
End If
cnt = 1
iInt = Int((lNumber / (lStr ^ cnt)))
Do Until iInt = 0
    iMod = iInt Mod lStr
    If iMod > 0 Then
        strRes = aStr(iMod - 1) & strRes
    Else
        strRes = aStr(UBound(aStr)) & strRes
        iInt = iInt - (UBound(aStr) + 1)
    End If
    cnt = cnt + 1
    intCK = CDec((lNumber / (lStr ^ cnt)))
    If intCK - Int(intCK) = 0 And (intCK < cnt) Then Exit Do
    iInt = Int(intCK) + (intCK - Int(intCK) = 0)
Loop
dhStrNum = strRes
End Function

 

[Rick Rothstein]

To convert from string back to number,

Function ToNum(sCol As String, sSym As String) As Long
    ' shg 2014
    
    If Len(sCol) Then ToNum = InStr(1, sSym, Right(sCol, 1), vbTextCompare) _
       + Len(sSym) * ToNum(Left(sCol, Len(sCol) - 1), sSym)
End Function

E.g., =ToNum("ARMAUJBH", "ABCDFGHIJKMQRSUVWXYZ") returns 2,147,483,647

Here is another way to calculate that using an explicit loop instead of the "hidden" one that your recursive function uses...

Function ToNum(ByVal sCol As String, sSym As String) As Long
  Dim X As Long
  For X = 1 To Len(sCol)
    ToNum = ToNum + InStr(sSym, Mid(sCol, X, 1)) * 20 ^ (Len(sCol) - X)
  Next
End Function

Yeah, yeah, I know, look who is suggesting a multiline solution in place of your one-liner... personally, I do not like recursive functions.

NOTE: Neither of our codes react to incorrect values being passed in the first argument (for example, they both return 820 for BEZ whereas 820 is actually the value for AZZ... the letter E was not part of the letter set passed into the second argument).

 

[shg]

    ToNum = ToNum + InStr(sSym, Mid(sCol, X, 1)) * [COLOR="#FF0000"]20[/COLOR] ^ (Len(sCol) - X)

That assumes the length of the symbol string, does it not?

 

[Rick Rothstein]

    ToNum = ToNum + InStr(sSym, Mid(sCol, X, 1)) * [COLOR=#ff0000]20[/COLOR] ^ (Len(sCol) - X)

That assumes the length of the symbol string, does it not?

Yes it does (I apparently missed that when generalizing the code from my specific setup used to proof the code)... good catch! Here is the corrected code...

Function ToNum(ByVal sCol As String, sSym As String) As Long
  Dim X As Long
  For X = 1 To Len(sCol)
    ToNum = ToNum + InStr(sSym, Mid(sCol, X, 1)) * Len(sSym) ^ (Len(sCol) - X)
  Next
End Function

 

[Rick Rothstein]

Yes it does (I apparently missed that when generalizing the code from my specific setup used to proof the code)... good catch! Here is the corrected code...

Function ToNum(ByVal sCol As String, sSym As String) As Long
  Dim X As Long
  For X = 1 To Len(sCol)
    ToNum = ToNum + InStr(sSym, Mid(sCol, X, 1)) * Len(sSym) ^ (Len(sCol) - X)
  Next
End Function

Although I am pretty sure there is a way to increase the range for your function (making it able to return numbers greater than a Long), doing so with my code is extremely simple... all I have to do is change the function's data type to Variant and appy a CDec function conversion to the accumulating total...

Function ToNum2(ByVal sCol As String, sSym As String) As Variant
  Dim X As Long
  For X = 1 To Len(sCol)
    ToNum2 = CDec(ToNum2) + InStr(sSym, Mid(sCol, X, 1)) * Len(sSym) ^ (Len(sCol) - X)
  Next
End Function

The above function will work with values up to ZZZZZZZZZZZZZZZZZZZZZZ (there are 22 Z's) producing a decimal (string) value of 44150568421052631578947368420 (a number with 29 significant figures).

 

[shg]

Just change the function type to Double and you can get the largest integers a Double will support.

 

[Rick Rothstein]

Just change the function type to Double and you can get the largest integers a Double will support.

That appears to be ZZZZZZZZZZZ (11 Z's) producing a result of 215578947368420 (16 significant figures). Actually, though, now that I looked at it in detail, your function converts just as easily as mine...

Function ToNum(ByVal sCol As String, sSym As String) As Variant
    If Len(sCol) Then ToNum = CDec(InStr(1, sSym, Right(sCol, 1), vbTextCompare)) _
       + Len(sSym) * ToNum(Left(sCol, Len(sCol) - 1), sSym)
End Function

 

[shg]

Sure, but what good is a number that the Excel UI can't calculate with?