convert column number to letter

[lvcha.gouqizi]

If I know a cell's column number is 25, how can I know what letter its
column represent? Just like that, the column 2 is actually column "B".
Thanks.

[Crowbar via OfficeKB.com]

Try Tools menu . Options > General and check/uncheck view R1C1 Style to
switch between formats

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/excel-programming/200510/1

[lvcha.gouqizi]

thanks. but how to implement the conversion in VBA code?

[Bob Phillips]

=IF(A1>26,CHAR(64+INT(A1/26))&CHAR(64+MOD(A1,26)),CHAR(64+A1))

assuming the column number is in A1

--

HTH

RP
(remove nothere from the email address if mailing direct)

"lvcha.gouqizi" <lvcha....@gmail.com> wrote in message
news:1130182195.2...@f14g2000cwb.googlegroups.com...

[Bob Phillips]

Why didn't you say?

'-----------------------------------------------------------------
Function ColumnLetter(Col As Long)
'-----------------------------------------------------------------
Dim sColumn As String
sColumn = Split(Columns(Col).Address(, False), ":")(1)
ColumnLetter = sColumn
End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)

"lvcha.gouqizi" <lvcha....@gmail.com> wrote in message

news:1130183550.0...@g14g2000cwa.googlegroups.com...

[Harald Staff]

Hi

You'll find all variations here, from short to "interesting":
http://www.dicks-blog.com/archives/2004/05/21/column-numbers-to-letters/

HTH. Best wishes Harald

"lvcha.gouqizi" <lvcha....@gmail.com> skrev i melding
news:1130183550.0...@g14g2000cwa.googlegroups.com...

[lvcha.gouqizi]

sorry for the confusion. Thanks a lot, it works!

[Dick Kusleika]

Harald Staff wrote:
> Hi
>
> You'll find all variations here, from short to "interesting":
> http://www.dicks-blog.com/archives/2004/05/21/column-numbers-to-letters/
>

Ooh, look how prescient I am. :)

--
Dick Kusleika
MVP - Excel
Daily Dose of Excel
http://www.dicks-blog.com

[lvcha.gouqizi]

I cannot connect to this webpage. What's it for?

[Tom Ogilvy]

A bit less intense: If the column number is in A1

=LEFT(ADDRESS(ROW(),A1,4,TRUE),1+(A1>26))

--
Regards,
Tom Ogilvy

"lvcha.gouqizi" <lvcha....@gmail.com> wrote in message

news:1130184893.4...@g43g2000cwa.googlegroups.com...

[Gord Dibben]

Try this User Defined Function

Function GetColLet(ColNumber As Integer) As String
GetColLet = Left(Cells(1, ColNumber).Address(False, False), _
1 - (ColNumber > 26))
End Function

usage is =GetColLet(25) will return Y

Can be called from a Sub

Sub getlet()
Dim numcol As Integer
numcol = InputBox("enter a number")
MsgBox "The column is " & GetColLet(numcol)
End Sub

Gord Dibben Excel MVP

On 24 Oct 2005 12:29:55 -0700, "lvcha.gouqizi" <lvcha....@gmail.com>
wrote:

[Dick Kusleika]

lvcha.gouqizi wrote:
> I cannot connect to this webpage. What's it for?

It's an excel blog. Try www.dailydoseofexcel.com

--
Dick Kusleika
MVP - Excel

Excel Blog - Daily Dose of Excel
www.dicks-blog.com

[Leith Ross]

Here is a flexible VBA macro that can also be used on the Worksheet to
convert a number to the correct column letters. Just load the code into
a VBA module to use it.

WORKSHEET FORMULA EXAMPLE:

=ColumnLtr(38)

The cell will contain "AL"
This can also be used in VBA code

VBA CODE USAGE:

Col = ColumnLtr(256)

Col will contain "IV"

Code:
--------------------
Public Function ColumnLtr(ByVal Column_Number As Long) As String

'Converts a number to a Column Letter

Dim Ltr As String
Dim N1 As Long
Dim N2 As Long

'Maximum Column value is 256
If Column_Number > 256 Then Column_Number = Colummn_Number Mod 256

'Convert to Column_Number Base 26
N1 = Column_Number \ 26
N2 = Column_Number Mod 26

'Convert number to Alpha characters
If N1 <> 0 Then
Ltr = Chr$(64 + N1)
End If

If N2 = 0 Then
Ltr = Ltr & "A"
Else
Ltr = Ltr & Chr$(64 + N2)
End If

ColumnLtr = Ltr

End Function

--------------------

--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=18465
View this thread: http://www.excelforum.com/showthread.php?threadid=478915

[Leith Ross]

Hello lvcha.gouqizi ,

Here is a flexible macro that can used as an Excel Worksheet Formula or
in VBA code. It translates a number to its Excel column letter
equivalent. It also checks the number. Numbers < 1 return an empty
string, and numbers greater than 256 are divided by 256 and the
remainder is used.

WORKSHEET FORMULA:
=ColumnLtr(40)

The cell will contain "AN".

VBA CODE:
-<string>- = ColumnLtr(<-long integer->)

Code:
--------------------
Public Function ColumnLtr(ByVal Column_Number As Long) As String

'Converts a number to a Column Letter

Dim Ltr As String
Dim N1 As Long
Dim N2 As Long

'Column must greater than 0
Column_Number = Column_Number - 1
If Colimn_Number < 0 Then
ColumnLtr = ""
Exit Function
End If

'Maximum Column value is 256
If Column_Number > 256 Then Column_Number = Colummn_Number Mod 256

'Convert to Column_Number Base 26
N1 = Column_Number \ 26
N2 = Column_Number Mod 26

'Convert number to Alpha characters

If N1 = 0 Then
Ltr = ""
Else

Ltr = Chr$(64 + N1)
End If

Ltr = Ltr & Chr$(65 + N2)

[lvcha.gouqizi]

Awesome work!
Thank you very much, Leith.

[Bob Phillips]

I think my suggestion is better, and it won't accept an invalid column, such
as 257.

--

HTH

RP
(remove nothere from the email address if mailing direct)

"lvcha.gouqizi" <lvcha....@gmail.com> wrote in message

news:1130246883....@f14g2000cwb.googlegroups.com...

[Crowbar via OfficeKB.com]

Convert the sheet to letter:-

With Application
.ReferenceStyle = x1R1C1
.EnableSound = False
.RollZoom = False
End With
End Sub

Convert the sheet to numbers:-

With Application
.ReferenceStyle = xlR1C1
.StandardFont = "Arial"
.StandardFontSize = "10"
.EnableSound = False
.RollZoom = False
End With
End Sub

--
Message posted via http://www.officekb.com