# Convert column number to letter

This is a discussion on Convert column number to letter within the Excel Questions forums, part of the Question Forums category; I can obtain the columns numbers but I cannot get the letters. Is there anyway to convert from a number ...

1. ## Convert column number to letter

I can obtain the columns numbers but I cannot get the letters. Is there anyway to convert from a number to a letter?
eg. somefunction(1) gives me column(A) as an answer?

Thanks.

2. ## Re: Convert column number to letter

3. hi!
good only for column A to Z

=CHAR(64+COLUMN(A2))

4. ## Re: Convert column number to letter

Aladin's code works fine...

Address function works as Address(row,column) to return an absolute reference

eg

All you need to do is ADD the number of columns you want to the column reference

ie

and it will return BM.

Nice Aladin as per usual

5. ## Re: Convert column number to letter

Thanks for the help guys. I initially wanted to do this so I could move along the column putting a header in each populated column as i thought Range("A1") was the only way. Since you were asking why I realised there must be another way and now I am using cells(row,col).

Thanks again.

6. ## Re: Convert column number to letter

Are you using this formula then

in say Cell B4 to get the result "B"

7. ## Re: Convert column number to letter

No I have just set up a for loop that goes through all my columns inserting a number until it gets to the last one. eg.

For x=1 TO NumOfCols
Cells(1,x).value = x

Something like that
I dont have the code with me.

Could I get someone to explain the following portion of the formula/function and how it is converting the arguments from Aladin's formula?
COLUMN(),4),"1","")

I see Address is using 1 as the row reference.
Column() w/out any argument and then 4 as an abs_num is the first part of the formula I do not understand.

Thanks,

ds

9. Doug,
Address(rownumber,columnnumber) returns a string with the address of the cell equivalent of ActiveSheet.Cells(rownumber,ColumnNumber). The thir argument in the address function forces the Address function to return a string representing a relative reference.

For example,
=Address(1,2,4) would return "B1"

Because the rownumber fed to the address function in this case is always 1, the substitute function is used to replace 1 with "", leaving the column Letter(s).

=Column() returns the column number of the hose cell, so
=Address(1,2,4) would return a string containing the relative reference of the first cell in the column of the host cell.

10. Thanks PA,
That helped explain perfectly.

Cheers,

Doug

Page 1 of 3 123 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•