Geek Answers Handbook

How can I get the letters of a cell column (I need to make it work on column Z, for example, AA, AB)

Possible duplicate:
VBA function to convert column number to letter?

I want the letters of the columns to go through the Z columns, there are many methods to get it before the Z column, but nothing works after Z. Is there a way to do this?

+7
source share
3 answers

Another method:

Public Function ColumnLettersFromRange(rInput As Range) As String ColumnLettersFromRange = Split(rInput.Address, "$")(1) End Function
+13
source

This should complete the task:

  Function ColumnName (rng As Range) As String
     Dim s As String
     s = rng.Address (False, False)
     ColumnName = Left (s, Len (s) - Len (Format (rng.Row, "0")))
 End function
+6
source

Using worksheet functions to calculate a column letter is not really a good plan. Using Peter Albert's VBA method is a much better way to do this!

I had the desire to do one, using the functions of the worksheet, just for fun: /

A-ZZ

 =IF(A1<27,CHAR(64+A1),IF(A1<703,CHAR(64+INT(A1/26))&CHAR(A1-INT(A1/26)+64),"TOO BIG!"))

A-XFD (not working)

 =IF(A1<27,CHAR(64+A1),IF(A1<703,CHAR(64+INT(A1/26))&CHAR(A1-(INT(A1/26)*26)+64),CHAR(64+INT(A1/676))&CHAR(64+(INT(A1-(INT(A1/676)*676))/26))&CHAR(64+INT(A1-((INT(A1-(INT(A1/676)*676))/26)*26)))))

The final will fall on trying to work out the 3rd character in the address, I just can not worry, because using it is not very good!

+1
source

More articles:


All Articles