Ask Your Question
0

How can I get the current cell Column letter ?

asked 2019-02-04 20:06:50 +0100

fabe gravatar image

When cell is located between A and Z Column, the formula:

=CHAR(column()+64) works till Z, afterwards the thing goes wird. Is there a better way to achieve this for the whole row and even for the beginning part that span from A to Z ?

Thanks in advance

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2019-02-04 21:27:50 +0100

Hello @fabe.

The thing goes weird, cause =CHAR(column()+64) formula returns a character from ASCII table according to nested COLUMN()+64formula result. So for column A (which is column 1), CHAR(65) returns character "A" from ASCII table, column B -> "B" and so on till "Z", cause both character in ASCII table and column labels are placed in alphabetical order. Then, starting from CHAR(91), which is AA column in Calc and "[" character in ASCII table, result from CHAR() formula does not match column label anymore, cause there are no any character combinations like AA, BD, ZZ and so on.

The better solution by @JohnSUN is found in this topic. Use formula =SUBSTITUTE(ADDRESS(1;COLUMN();4);"1";"")

edit flag offensive delete link more

Comments

Exactly what I meant, awesome thanks very much

fabe gravatar imagefabe ( 2019-02-05 16:36:21 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-02-04 20:06:50 +0100

Seen: 311 times

Last updated: Feb 04