Macro to convert numbers to letters? 1

[Brianfree]

Hi, please can anyone help on converting a number to a letter

so 0 = a, b = 1, c = 2, etc..

For Example

Col 1, Col 2
10, BA
55, FF
60, GA
200, CAA


Many thanks

Brian

 

[ClulessChris]

what application are you using? If it's Excel you could maybe use Cells method to find the number of a column from the Column address?

Never knock on Death's door: ring the bell and run away! Death really hates that!

 

[Brianfree]

Hi, I am using Excel. I have a function to convert letters to numbers but the other way around!

I can find lots of examples or converting columns but not actual row data!

Thanks fro replying ;-)

 

[strongm]

[blue]Public Function letters(ByVal Source As Variant) As String
    Dim mychar As Variant
    Dim numberchars() As Byte
    
    numberchars = StrConv(CStr(Source), vbFromUnicode)
    
    For Each mychar In numberchars
        letters = letters + Chr$(mychar - Asc("0") + Asc("A"))
    Next
End Function[/blue]

 

[Brianfree]

Thanks strongm, perfect! matter of interest how could I do this in reverse? ;-)

Many thanks

Brian

 

[strongm]

I had a suspicion you'd ask, which si why the function was written the way it was. Have a look at the following, see if it looks familiar ...

[blue]Public Function numbers(ByVal Source As Variant) As String
    Dim mychar As Variant
    Dim numberchars() As Byte
    
    numberchars = StrConv(CStr(Source), vbFromUnicode)
    
    For Each mychar In numberchars
        numbers = numbers + Chr$(mychar - Asc("A") + Asc("0"))
    Next
End Function[/blue]

 

[zelgar]

Here's another macro (though not as elegant as strongm's)

Sub num_to_letter()

Dim irow As Integer
Dim x As Variant
Dim x_ilog As Integer
Dim y As Integer
Dim letter$

irow = 1  'Starting Row
Do Until IsEmpty(Cells(irow, 1))
    letter$ = ""
    x = Cells(irow, 1)
    x_ilog = Int(Log(x) / Log(10#)) + 1
    For i = 1 To x_ilog
        y = Int(x / (10 ^ (x_ilog - i)))
        If i > 1 Then y = 10 * (y / 10 - Int(y / 10))
        letter$ = letter$ & Chr$(65 + y)
    Next i
Cells(irow, 2) = letter$
irow = irow + 1
Loop

End Sub

 

[zelgar]

Note, my macro was to go from number to character.