Get Letter from Number

alketrazz

alketrazz

New Member
Joined
Nov 13, 2013
Messages
23
Office Version
  1. 365
Platform
  1. Windows
Hi

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

Code: 
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.

Code: 
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Try this:-
NB:- "Rng" relates to a separate cell with your required number.
Code: 
Function TxT(Rng As Range) As String
Dim n As Long
Dim Str As String
Dim S As String
Str = "ABCDFGHIJKMQRSUVWXYZ"
 For n = 1 To Len(Rng)
    S = S & Mid(Str, Mid(Rng, n, 1), 1)
 Next n
TxT = S
End Function
 
Upvote 0
Hi

This isn't working. If I put '21' in a cell I get BA. 21 would be 'AA', 22 = 'AB' and so on.

Also, it wont work with any zero's (20 returns an error)

My first code didn't do as I said anyway.

This is what I described.

Code: 
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)
    GetAvailableLetter = GetAvailableLetter & Mid(strSearch, iPos, 1)
  End If


  iPos = iNum Mod iMax
  iPos = IIf(iPos = 0, iMax, iPos)
  GetAvailableLetter = GetAvailableLetter & Mid(strSearch, iPos, 1)


End Function

It works up until 421 which is ZZ then it just goes back to A.

I think I need to used a do loop for the length of the number

Thanks
 
Upvote 0
This should should go to just under 9K
Could be improved ,but its a start !!!
Rich (BB code): 

	

		Code:

	

	

		
Function AphN(ByRef Num [COLOR=Navy]As[/COLOR] Long) [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] A [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] B [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] C [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] Str [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] k [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
Str = "ABCDFGHIJKMQRSUVWXYZ"
[COLOR=Navy]
If[/COLOR] Num > 8820 [COLOR=Navy]Then[/COLOR]
    AphN = "Num Too Large"
[COLOR=Navy]Else[/COLOR]
    [COLOR=Navy]For[/COLOR] C = 1 To Num
        A = A + 1
        [COLOR=Navy]If[/COLOR] C = Num [COLOR=Navy]Then[/COLOR]
            [COLOR=Navy]If[/COLOR] n = 0 [COLOR=Navy]Then[/COLOR]
                AphN = Mid(Str, A, 1)
            [COLOR=Navy]ElseIf[/COLOR] C > 20 And C <= 420 [COLOR=Navy]Then[/COLOR]
                AphN = Mid(Str, n, 1) & Mid(Str, A, 1)
            [COLOR=Navy]ElseIf[/COLOR] C > 420 [COLOR=Navy]Then[/COLOR]
                AphN = Mid(Str, k, 1) & Mid(Str, n, 1) & Mid(Str, A, 1)
            [COLOR=Navy]End[/COLOR] If
        [COLOR=Navy]End[/COLOR] If
            A = IIf(A = 20, 0, A)
            [COLOR=Navy]If[/COLOR] C Mod 20 = 0 [COLOR=Navy]Then[/COLOR]
                n = IIf(n = 20, 0, n)
                n = n + 1
                [COLOR=Navy]If[/COLOR] C Mod 420 = 0 [COLOR=Navy]Then[/COLOR] k = k + 1
            [COLOR=Navy]End[/COLOR] If
   [COLOR=Navy]Next[/COLOR] C
[COLOR=Navy]End[/COLOR] If
[COLOR=Navy]End[/COLOR] Function

	


Regards Mick
 
Upvote 0
Hi Mick

That's spot on thank you

When I started that I thought it would be fairly easy but it really had me stumped.

I've been trying with a looping element with stripping parts down and then working each part out separately.

i.e. 438, would be 400, 38, 8 and then getting the starting points for each last point if that makes sense.

Your solution does what at I need as it would never go past 3 letters but if I get my other solution working I'll post it

Thanks again

Kev
 
Upvote 0
Here is another solution to explore:



Code: 
Function dhStrNum(lNumber As Integer) As String
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)
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
    iInt = Int((lNumber / (lStr ^ cnt)))
Loop
dhStrNum = strRes
End Function
 
Upvote 0
Mick, I think your code returns "AAA" for both 421 and 821.

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

Code: 
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
 
Last edited:
Upvote 0
Or, to geeralize it and put the symbol string in the signature,

Code: 
Function GetLtrs(ByVal i As Long, s As String) As String
    ' Works for any positive Long
    
    If i > 0 Then GetLtrs = GetLtrs((i - 1) \ Len(s), s) & Mid(s, ((i - 1) Mod Len(s)) + 1, 1)
End Function

A​
B​
C​
1​
Num​
Ltrs​
​
2​
1​
AB2: =GetLtrs(A2, "ABCDFGHIJKMQRSUVWXYZ")
3​
10​
K
4​
20​
Z
5​
100​
DZ
6​
200​
JZ
7​
1,000​
BJZ
8​
2,000​
DYZ
9​
10,000​
ADYZ
10​
20,000​
BJYZ
11​
100,000​
QJYZ
12​
200,000​
ADYYZ
13​
1,000,000​
GDYYZ
14​
2,000,000​
QJYYZ
15​
10,000,000​
CBJYYZ
16​
20,000,000​
GDYYYZ
17​
100,000,000​
AMDYYYZ
18​
200,000,000​
CBJYYYZ
19​
1,000,000,000​
UQJYYYZ
20​
2,000,000,000​
AMDYYYYZ
21​
2,147,483,647​
ARMAUJBH
 
Upvote 0
Perhaps
Code: 
Sub test()
    Dim i As Long, Letters As String
    For i = 1 To 20
        Letters = Letters & Chr(64 + i)
    Next i
    Range("B1").Value = Letters: Rem "ABCD...QRST"

    With Range("A1:A500")
        .FormulaR1C1 = "=NumeralFromCount(ROW(), R1C2)"
    End With
End Sub

Function NumeralFromCount(aNumber As Long, Alphabet As String) As String
    Dim i As Long
    NumeralFromCount = Left(Alphabet, 1)
    For i = 1 To aNumber - 1
        NumeralFromCount = NextNumeral(NumeralFromCount, Alphabet)
    Next i
End Function

Function NextNumeral(aNumeral As String, Alphabet As String, Optional ByVal Overflow As Boolean) As String
    Dim lastChr As String, firstChr As String, nextChr As String
    Dim point As Long
    firstChr = Left(Alphabet, 1): lastChr = Right(Alphabet, 1)
    point = Len(aNumeral)
    Do
        nextChr = Mid(Alphabet & Alphabet, InStr(1, Alphabet, Mid(aNumeral, point, 1)) + 1, 1)
        Mid(aNumeral, point, 1) = nextChr
        point = point - 1
    Loop Until point < 1 Or nextChr <> firstChr
    Overflow = (nextChr = firstChr)
    If Overflow Then aNumeral = firstChr & aNumeral
    NextNumeral = aNumeral
End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Question
How to find an exact number match in a text file?
Replies
9
Views
784
frankT68
F
T
  • Solved
Remove numbers from dynamic range
Replies
5
Views
276
Georgiboy
Georgiboy
T
  • Question
Insert/Delete Columns issue via VBA
Replies
1
Views
657
Tdorman
T
L
  • Question
Formula & Sub to format phone numbers working by itself but not in conjunction with other code.
Replies
4
Views
191
Scott Huish
Scott Huish
Y
  • Solved
byref argument type mismatch
Replies
1
Views
695
Alex Blakenburg
A

Forum statistics

Threads
1,214,965
Messages
6,122,500
Members
449,090
Latest member
RandomExceller01

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top