Convert column number to letter

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Jan 17, 2004

• #2

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

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

 

Upvote 0

SIXTH SENSE

Well-known Member

Joined

Oct 29, 2003

Messages

1,883

• Jan 17, 2004

• #3

hi!
good only for column A to Z

=CHAR(64+COLUMN(A2))

 

Upvote 0

DonkeyOte

MrExcel MVP

Joined

Sep 6, 2002

Messages

9,124

• Jan 17, 2004

• #4

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

 

Upvote 0

F

frustrated123

New Member

Joined

Jan 13, 2004

Messages

9

• Jan 19, 2004

• #5

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.

 

Upvote 0

K

ken2step

Well-known Member

Joined

Jan 9, 2003

Messages

642

• Jan 19, 2004

• #6

Are you using this formula then

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

in say Cell B4 to get the result "B"

 

Upvote 0

F

frustrated123

New Member

Joined

Jan 13, 2004

Messages

9

• Jan 19, 2004

• #7

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.

 

Upvote 0

D

DougStroud

Well-known Member

Joined

Aug 16, 2005

Messages

2,976

Office Version

1. 365

Platform

1. MacOS

• Jun 25, 2007

• #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

 

Upvote 0

PA HS Teacher

Well-known Member

Joined

Jul 17, 2004

Messages

2,838

• Jun 25, 2007

• #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).

[Edit]
=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.

 

Upvote 0

D

DougStroud

Well-known Member

Joined

Aug 16, 2005

Messages

2,976

Office Version

1. 365

Platform

1. MacOS

• Jun 25, 2007

• #10

Thanks PA,
That helped explain perfectly.

Cheers,

Doug

 

Upvote 0