PDA

View Full Version : Solved: Convert number to letter



enfantter
03-06-2009, 03:48 AM
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
03-06-2009, 04:03 AM
you can use this user defined formula:


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

Bob Phillips
03-06-2009, 04:07 AM
This gets the letter of the number in G1

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

Bob Phillips
03-06-2009, 04:08 AM
or even

=CHAR(G1+64)

enfantter
03-06-2009, 04:09 AM
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
03-06-2009, 04:11 AM
wow - always on the watch xld
exactly what i needed !

enfantter
03-06-2009, 04:15 AM
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
03-06-2009, 04:54 AM
My response in #'3 does just that, put 27 in G1 and see what you get.

faarikaal
03-06-2009, 04:55 AM
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

MaximS
03-06-2009, 04:58 AM
try that:



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

enfantter
03-06-2009, 05:36 AM
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
03-06-2009, 05:37 AM
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
03-06-2009, 05:49 AM
Sorry,

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

enfantter
03-06-2009, 05:54 AM
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
03-06-2009, 06:24 AM
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
03-06-2009, 08:22 AM
Try that:


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



you can easily convert it to sub.

enfantter
03-09-2009, 01:19 AM
xld - it works now! cheers