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

 

[Rick Rothstein]

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

I don't think we don't know that the OP needs Excel to calculate with it (maybe it is just a special kind of indexer). If the OP did need to calculate with the number, and depending on what kind of calculations he needed, we could probably provide VBA code to perform it (shouldn't be a problem for the OP given he would be using VBA code to calculate the number). Besides, and most important... it's a weekend, things are slow, I'm somewhat bored and I wanted to see how far the functions could be taken.

 

[shg]

 

[Snakehips]

Here's another option that works up to 2^31-1 ("ARMAUJBH")

Function GetLtrs(ByVal i As Long) As String
    ' shg 2012
    ' Works for any positive Long

    Const s As String = "ABCDFGHIJKMQRSUVWXYZ"

    If i > 0 Then GetLtrs = GetLtrs((i - 1) \ Len(s)) & Mid(s, ((i - 1) Mod Len(s)) + 1, 1)
End Function

Respect!!!

Sadly, only just refreshed this page.

I'd spent two days on and off approaching this, along similar lines to Mick and Geek. The result was always 'close but no cigar'.
Eventually I decided erase my code, give up thinking about it and to get a life.

As not even you 'big hitters' had weighed in with a solution by then I was beginning to think there was no complete solution to be had.

Despite that, I awoke this morning convinced that I had solved it overnight, in my sleep, although further coding this morning proved that to be ....... 6=7!!

I do regret not refreshing the page some 20 hours or so earlier!!

 

[alketrazz]

Hi all

Well firstly, thanks so much for helping with this.

My code started to grow and grow but I still wasn't getting it. Then I see the responses on here and it's laughable how long my code was getting.

I like the bit about returning the numbers from the letters as well. I don't need to calculate it as mentioned in one post but it's good to read the ideas and responses from this.

Glad this got a bit of interest as well.

Thanks again guys, spot on...

 

[alketrazz]

Thanks again guys, spot on...

Function GetLtrs(ByVal i As Long) As String ' shg 2012 ' Works for any positive Long Const s As String = "ABCDFGHIJKMQRSUVWXYZ" If i > 0 Then GetLtrs = GetLtrs((i - 1) \ Len(s)) & Mid(s, ((i - 1) Mod Len(s)) + 1, 1)End Function</pre>

I still can't work out what this is doing, I get that it keeps calling itself but if the number is 800, why does it only loop three times.

Maybe just way out of my league and I shouldn't ask to many questions that I don't understand

Thanks

 

[shg]

Because it removes a symbol each time it calls itself. When the length gets to zero, it unwinds.

 

[shg]

Right explanation, wrong function

If i > 0 Then GetLtrs = GetLtrs([COLOR="#FF0000"](i - 1) \ Len(s)[/COLOR]) & Mid(s, ((i - 1) Mod Len(s)) + 1, 1)

Each time the function calls itself, it divides the number argument by the length of the symbols string. When it gets to zero, it unwinds.