Excel – how do we find the
Column Number?
Every Excel user knows that an address
of a cell comprises of a column (Alphabetic)
and a row (numeric).
But the column name represents a number:
Column A is the first, therefore its number is: 1,
Column B is the second, therefore its number is: 2 and so on.
But have you ever wondered: How do we find the column NUMBER from its name?
What column number is: ABC, QZD, FFF, DAT, RRR, X etc.???
What's the solution?
There are, actually, two solutions:
The first, very simple and straightforward:
For example, if you want to find the number
of column AAA,
Just type the following formula:
=COLUMNS(A:AAA)
And the result: 703
Pic 1: Find the column's number – method 1
The second is a formula that I've developed,
which yields the same result.
A short explanation of my formula:
The English alphabet has 26 letters, from A to Z.
So, for example, if we're on column Z, then we're on column no.26.
If the column name consists of 2
letters (for example: AA), then we need to multiply the first letter A
(representing 1) by 26 and then add 1 (the second A).
If the column name consists of 3
letters (for example: AAA), then we need to multiply the first letter A
(representing 1) by 26^2, and then:
add 1 (the second A) multiplied by 26 and finally:
add the third A (which is 1),
So: AAA = 1*26^2 + 1*26^1 + 1*26^0 =
676+ 26 + 1 = 703
And why do we subtract 64?
Because the ASCII codes of the English
uppercase letters begin with: 65 (=A).
In order to find the number by the letter,
we need to subtract 64.
A (=65) becomes 1, B (=66) becomes 2…..
and Z (=90) becomes 26.
That's all.
Hope you enjoyed this post.
Please give it a thumbs-up.
Of
course, you could change the columns from alphabetic to numeric,
But
then, you won't be able to know what was the original columns' names….
Converting
the reference style to R1C1, thus:
אין תגובות:
הוסף רשומת תגובה