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
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";"")
Exactly what I meant, awesome thanks very much
There’s two senarios where the desire would be to get either the row or columbn value of a particular cell. (Does not work across/between Sheets!)
In cell F5 is the formula:
=CONCATENATE(
" -> "
,REGEX(REGEX(ADDRESS(ROW(),COLUMN(),4),"[^A-Z0-9]+","","g"),"[0-9]+","",1)
," / "
,REGEX(REGEX(ADDRESS(ROW(),COLUMN(),4),"[^A-Z0-9]+","","g"),"[A-Za-z]+","",1)
," <- "
)
When the formula is evaluated, you get two things:
These values come from the two “REGEX” lines shown. Both of them share the sequence of:
REGEX( ADDRESS( ROW(), COLUMN(), 4), "[^A-Z0-9]+", "", "g" )
This set of functions asks the system for the ROW() number internally and the COLUMN() which is then asked to return as an ADDRESS (type 4 means relative) F5 vs $F$5, etc.
The REGEX() function will strip out anything from the returned address that is not at least one upper case letter “A-Z” or a numeric digit “0-9”.
The outer REGEX function does the work of stripping off the numeric digits (first case) leaving the letters or the second one which strips off the letters (second case).
In cells A1-A5 are the folowing values (text strings or values of formulas)
| A | |
|---|---|
| 1 | B2 |
| 2 | $B2 |
| 3 | B$2 |
| 4 | $B$2 |
| 5 | ZZ99 |
Now, in cell “B1” we put this formula:
1|=CONCATENATE(
2| " -> "
3| , REGEX(
4| REGEX(
5| INDIRECT(
6| ADDRESS(
7| ROW(A1)
8| ,COLUMN(A1)
9| ,4
10| ,1
11| )
12| )
13| ,"[^A-Z0-9]+"
14| ,""
15| ,"g"
16| )
17| ,"[0-9]+"
18| ,""
19| ,1
20| )
21| , " / "
22| , REGEX(
23| REGEX(
24| INDIRECT(
25| ADDRESS(
26| ROW(A1)
27| ,COLUMN(A1)
28| ,4
29| ,1
30| )
31| )
32| , "[^A-Z0-9]+"
33| , ""
34| , "g"
35| )
36| , "[A-Za-z]+"
37| , ""
38| , 1
39| )
40| , " <- "
41|)
and then replicate it down column “B” aside of what’s in “A” and you’ll get:
| A | B | |
|---|---|---|
| 1 | B2 |
→ B / 2 ← |
| 2 | $B2 |
→ B / 2 ← |
| 3 | B$2 |
→ B / 2 ← |
| 4 | $B$2 |
→ B / 2 ← |
| 5 | ZZ99 |
→ ZZ / 99 ← |
Both types use the same underlying approach, the difference is the use of the INDIRECT() function which says "Return the value IN the cell, not just the address of the cell in question.
The value in the ROW() and COLUMN() function calls has to be the same thing in all cases. The function calls of ROW(A1) returns “1” and COLUMN(A1) returns “A” which is based on the strings “A1” or whatever value is placed there. (i.e. ROW(D9) returns “9” and COLUMN(D9) returns “D”)
So, using the values of D9 instead of A1 to help make it clearer.
The value of:
ADDRESS( ROW(D9), COLUMN(D9), 4, 1 )
returns “D9”. I know that it seems “long winded” to get back to “D9” but the arguments to ROW() or COLUMN() can be calculated values so it can be flexable to arrive at specific values.
The return from that function call is handed to the INDIRECT() function which says “return the value in the cell given in the argument”, which in this case is D9
And that’s the glorious results.
With no argument to ROW() or COLUMN() (Used just like that.) returns the value for the cell it is in. Giving arguments to those functions must be the same or else you’re on your own.