Convert column number to letter

[frustrated123]

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.

 

[hiker95]

frustrated123,


The below macro will do the following, from cell A1 To XFD2:

Excel Workbook
	A	B
1	1	2
2	A	B
3
Sheet1

Excel Workbook
	XFC	XFD
1	16383	16384
2	XFC	XFD
3
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ColumnNumberLetter()
Dim x As Long
For x = 1 To Columns.Count
  Cells(1, x).Value = x
  Cells(2, x) = Split(Columns(x).Address, "$")(2)
Next x
End Sub

Then run the ColumnNumberLetter macro.

 

[hiker95]

frustrated123,


A slightly different twist to the above macro, just in case you wanted to start in a different column (For x = 18 to Columns.Count):

Option Explicit
Sub ColumnNumberLetterV2()
Dim x As Long
For x = 1 To Columns.Count
  With Cells(1, x)
    .Formula = "=COLUMN()"
    .Value = .Value
  End With
  Cells(2, x) = Split(Columns(x).Address, "$")(2)
Next x
End Sub

 

[MPierce486]

Hey everyone, I hope to resurrect this thread. My question is, how do I use the address function to create a range.For example, =address(1,1,4,1) : address(5,1,4,1)I want to use these functions to represent a range which is being used in a match function.Thanks!Matt

 

[patbarb]

Hi Matt, you may consider changing this to a new thread (unless I'm misunderstanding and this has to do with a VBA solution). At any rate, the use of Address definitely takes some getting used to! I've used it some but still had to fire up Excel and play around to see what was needed. -)

I suspect what might be throwing you is that Address returns a *text string* reference to a cell address rather than the cell address itself. That is to say, when you type in Address(1,1) you get 'A1' text back which Excel just sees as random text and not as being a reference to the actual cell A1 at all.

Enter the Indirect function. Ta-dah! Its sole job is to to tell Excel that the text string 'A1' is really meant to be a reference to the cell A1.

So, to recap:
Address(1,1) = 'A1' text string
Indirect(Address(1,1)) = A1, the actual cell reference

Taking it further, if you need an array, just concatenate the two Address functions and wrap it all in Indirect:
Indirect(Address(1,1) & ":" & Address(10,1)) = A1:A10, the actual array

Finally, stringing it all together to say "Find a match for B1 contents in the array A1:A10":
=MATCH(B1, Indirect(Address(1,1) & ":" & Address(10,1)), 0)

--Patrick

P.S. I see in your example you're playing around with the optional 3rd and 4th Address parameters ("=address(1,1,4,1)...") but I've always ignored them.

 

[sika85]

No super intuitive way on other words. You can also go with using range(cells(r#,c#), cells(r#,c#)) instead. For example: range(cells(1,2), cells(2,2)) which is B1:B2

 

[abmati]

The following formula will give the column reference (Text) of the cell (valid for column "A" to "Z")
=MID(ADDRESS(COLUMN(),COLUMN()),2,1)