Solved: Convert number to letter

[enfantter]

Hi all,

Probably simple question.

I would like to find a formular for converting a number in excel to the equivalent letter from the alphabet. I guess this should be the opposite of the code() - function.

I would preferable use an excel function if this is possible.
can anyone help?!
/Enfant Terrible

[MaximS]

you can use this user defined formula:

[vba]
Function N2L(ByVal Rng As Range)
If Rng.Value > 0 And Rng.Value < 27 Then
N2L = Chr(Rng.Value + 64)
Else
N2L = "Error"
End If
End Function
[/vba]

[Bob Phillips]

This gets the letter of the number in G1

=LEFT(ADDRESS(ROW(),G1,2),FIND("$",ADDRESS(ROW(),G1,2),1)-1)

[Bob Phillips]

or even

=CHAR(G1+64)

[enfantter]

just to make sure that i get this right.
there should be only one argument to N2L. That is a number (ex. 5) - the function would then return a letter (eg e)

[enfantter]

wow - always on the watch xld
exactly what i needed !

[enfantter]

Is it possible to make a formular that can actually convert a number to column description?! (eg 27 = AA)

sorry for the extra questions

[Bob Phillips]

My response in #'3 does just that, put 27 in G1 and see what you get.

[faarikaal]

[VBA]Public Function ColumnLetter(ColumnNumber As Integer) As String
If ColumnNumber > 26 Then

ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
Chr(((ColumnNumber - 1) Mod 26) + 65)
Else
' Columns A-Z
ColumnLetter = Chr(ColumnNumber + 64)
End If
End Function[/VBA]

[MaximS]

try that:


[vba]
Function N2L(ByVal x As Integer)
Dim y, z As Integer

If x > 0 And x < 257 Then
If x > 26 Then

If x Mod 26 = 0 Then
z = 26
y = Int(x / 26) - 1
Else
z = x Mod 26
y = Int(x / 26)
End If
N2L = Chr(y + 64) & Chr(z + 64)
Else
N2L = Chr(x + 64)
End If
Else
N2L = "Error"
End If
End Function
[/vba]

[enfantter]

OK sorry,
but when I use that one I get the following,

for all intergers up to (including) 26 i get $, then i get A and so forward.
Is it me who is doing it wrong?!

[Bob Phillips]

OK sorry,
but when I use that one I get the following,

for all intergers up to (including) 26 i get $, then i get A and so forward.
Is it me who is doing it wrong?!

Whose post is this responding to?

[enfantter]

Sorry,

Yours in #3,
you're probably on to what i wanna do

[enfantter]

Actually what i wanna do is to create a serie that consist of the name of every second column - maybe im missing something totally obvious ??

[Bob Phillips]

That formula in #3 will return A-Z,AA-IV, I don't see where $ comes into it.

For every second column letter use

=LEFT(ADDRESS(ROW(),COLUMN(A1)*2-1,2),FIND("$",ADDRESS(ROW(),COLUMN(A1)*2-1,2),1)-1)

this will return A, C, E, G, ...

[MaximS]

Try that:

[vba]
Function N2LS()
Dim Arr(0 To 130) As Variant
Dim i, j, k As Integer
Dim temp As String

For i = 1 To 10
For j = 1 To 26 Step 2
If i > 1 Then
Arr(k) = Chr(i - 1 + 64) & Chr(j + 64)
k = k + 1
Else
Arr(k) = Chr(j + 64)
k = k + 1
End If
Next j
Next i
temp = "A"
For i = 1 To 127
temp = temp & " " & Arr(i)
Next
N2LS = temp
End Function

[/vba]

you can easily convert it to sub.

[enfantter]

xld - it works now! cheers