?
Solved

Is there an Excel function which converts a column number to a column heading (letters) ?

Posted on 2011-05-04
9
Medium Priority
?
364 Views
Last Modified: 2012-05-11
Hi,

I'm returning a column reference as a number using the COLUMN command. Is there an Excel function which does the opposite - converts a column number into letters ? Or do I have to create a VBA function for this ?

Thanks
Toco
0
Comment
Question by:Tocogroup
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35688321
Yes :)

I use this. Paste this in a module.

Function ReturnName(ByVal num As Integer) As String
        ReturnName = Split(Cells(, num).Address, "$")(1)
End Function

Open in new window


Sid
0
 
LVL 50
ID: 35688329
Hello

use the ADDRESS() function

=ADDRESS(1,A1)

will return $M$1 if A1 contains 13 (which is column number 13 = column M)

You can then proceed to use that output in an INDIRECT formula, if required.

cheers, teylyn
0
 
LVL 21

Expert Comment

by:Alan
ID: 35688331
Hi,

The easy way would be:

=Char(ColumnNumber+64)

ie:

=Char(5+64)

= "E"

Column 5 = E

That will only work up to "Z" of course.

Alan.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21

Expert Comment

by:Alan
ID: 35688339
Hi,

Or if you have '5' (column E) and want to use it as a cell reference, then you could use OFFSET:

=OFFSET($A$1,0,5-1,1,1)

is the same as

= $E$1

HTH,

Alan.
0
 
LVL 50
ID: 35688348
You don't really need a custom VBA function. The ADDRESS() function already takes care of it.

The syntax is

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

From the Excel Help files:

row_num  Required. A numeric value that specifies the row number to use in the cell reference.
column_num  Required. A numeric value that specifies the column number to use in the cell reference.
abs_num  Optional. A numeric value that specifies the type of reference to return.

So you can return a complete cell reference. You can wrap the output into a LEFT() or RIGHT() function to extract just the row or column.

Again, no need for a custom VBA function, which will always be way slower than a native Excel function.

cheers, teylyn
0
 
LVL 50
ID: 35688367
This formula will return the column letters without resorting to VBA, which means it will work in any Excel workbook, and you do not have to enable macros for the formula to work.

=LEFT(ADDRESS(1,A1,4),LEN(ADDRESS(1,A1,4))-1)

Replace A1 with a cell reference or number of your choice.

cheers, teylyn
0
 

Author Comment

by:Tocogroup
ID: 35688457
Thanks Teylyn, that's exactly what I wanted.

However, as an aside, when I entered the formula and hit the return key, rather than display the value it displayed the actual formula in the cell !
Have I inadvertently formatted this cell to display as a formula rather than a value ? Calculation Options are set to Automatic.

I then entered a cell reference but it ignored it and displayed as if it was text...    =B5

Very strange...never come across this before.
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 35688538
It depends on what you want to return.

If you want the cell address, like A1, then use

=ADDRESS(1,A1) -- the result will be $M$1 if A1 - 13

If you want the column letter, use

=LEFT(ADDRESS(1,A1,4),LEN(ADDRESS(1,A1,4))-1) -- the result will be "M" if A1 = 13

If you want the content of the cell M1, then use

=INDIRECT(ADDRESS(1,A1,4))

A1 = 13 -- which is column M
1 =  the row number of the cell you want to return
4 = the code for the relative cell address

If all this does not help, please post a workbook and explain in context what you want to achieve.

cheers, teylyn
0
 

Author Closing Comment

by:Tocogroup
ID: 35688626
Many thanks. Worked a treat.
Toco
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question