Convert column number to letter

Thanks:  0
Likes:  0

# Thread: Convert column number to letter

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

=SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

=SUBSTITUTE(ADDRESS(1,COLUMN(A2),4),"1","")

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

Address(1,1) = \$A\$1

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

ie

=SUBSTITUTE(ADDRESS(1,COLUMN()+64,4),"1","")

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

=SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(B1),"")

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.

8. =SUBSTITUTE(ADDRESS(1,COLUMN(),4),"1","")

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

## User Tag List

#### Posting Permissions

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