Ask Your Question
0

is the a function that returns the column as a text?

asked 2020-04-03 14:10:55 +0200

Dianelos gravatar image

The column function returns the number of the column, so for example if I am in column A then =COLUMN() will return 1, and if I am in column BT then =COLUMN() will return 72.

I need a function which return the column identifier as text, so for example if I am in comumn BT I'd like to have "BT" returned to me.

edit retag flag offensive close merge delete

Comments

To get a cell address to call INDIRECT() with you can use ADDRESS(rowNum; colNum) directly. Concatenation of the address based on any workarounds is a useless complication in most cases.
Anyway the OFFSET() function should often be preferrable, in specific as it also easily creates references to cell ranges where the concatenation of range addresses is a mess..

Lupp gravatar imageLupp ( 2020-04-04 16:33:55 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2020-04-03 14:50:12 +0200

JohnSUN gravatar image

updated 2020-04-04 07:30:19 +0200

Try this

=SUBSTITUTE(ADDRESS(1;COLUMN();4);"1";"")
edit flag offensive delete link more

Comments

It works, thanks. Still, it's such a useful function for indirect addressing I thought there should be a native function.

Dianelos gravatar imageDianelos ( 2020-04-03 18:40:43 +0200 )edit

Concerning the coulumn names you have a rather efficient workaround as @JohnSUN showd you, and you actually can use the COLUMN() number for indirect addressing (preferrably).
It's different with sheets: You only get the SHEET() number, but to create an indirect reference into a different sheet you need tzhe name of the sheet. This is seriously aggravated if users insist of giving pseudonames containing spaces and special characters to sheets. These names need to be "singlequted" then with internall yinglequotes doubled. There is no "cheap" workaround at hand - and nbody seems to care.
Be happy to only want column names - and consider my suggestion to always use column numbers for indirect addressing. .

Lupp gravatar imageLupp ( 2020-04-04 16:24:41 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2020-04-03 14:10:55 +0200

Seen: 31 times

Last updated: Apr 04