Solved

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

Posted on 2011-05-04
Medium Priority
364 Views
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
Question by:Tocogroup
• 4
• 2
• 2
• +1

LVL 30

Expert Comment

ID: 35688321
Yes :)

I use this. Paste this in a module.

``````Function ReturnName(ByVal num As Integer) As String
End Function
``````

Sid
0

LVL 50

Expert Comment

ID: 35688329
Hello

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

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

LVL 21

Expert Comment

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

Expert Comment

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

The syntax is

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

Expert Comment

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.

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

cheers, teylyn
0

Author Comment

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

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

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

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

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

Featured Post

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â€¦
Suggested Courses
Course of the Month15 days, 21 hours left to enroll