Column() to return a letter instead of a number?

[cKBoy]

Can column() return a letter instead of a number? I am planning to use
it with INDIRECT? Is that possible?

=INDIRECT(row() & column())?

 

[broro183]

Hi
yes, it's possible, insert this into your formula:

CHAR(COLUMN()+64)

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[cKBoy]

Awesome! worked like a charm... Thanks!

 

[broro183]

pleased I could help
I hope the issue of columns greater than Z don't cause any problems...

Rob Brockett
NZ
Always learning & the best way to learn is to experience..

 

[cKBoy]

oh, =INDIRECT(CHAR(COLUMN(E5)+64)&ROW()) worked, but what if I wanted to
get the cell from a different sheet but the same cell location? ie.
=INDIRECT(Sheet1.CHAR(COLUMN(E5)+64)&ROW()) -- but this didn't work, is
there something missing from the function?

 

[Guest]

if column >=AA?

so better

=LEFT(ADDRESS(1,COLUMN(),4),IF(COLUMN()<27,1,2))

 

[Guest]

It works only until column Z, if cKBoy needs two-letter column identifiers
(AA, AB, ...) as well, he should use

=LEFT(ADDRESS(1,COLUMN(),4,1),IF(CODE(MID(ADDRESS(1,COLUMN(),4,1),2,1))>57,2,1))

Regards,
Stefi

„broro183†ezt írta:

 

[Guest]

It's interesting why obvious solutions don't come up sometimes!
Stefi


„ΓΙΑÎÎΗΣ Χ.Î’.†ezt írta:

 

[Guest]

try
=INDIRECT("sheet1!"&LEFT(ADDRESS(1,COLUMN(sheet1!E5),4),IF(COLUMN(sheet1!E5)<27,1,2))&ROW(sheet1!E5))

 

[Max]

=INDIRECT(Sheet1.CHAR(COLUMN(E5)+64)&ROW())

Try putting it as:
=INDIRECT("'Sheet1'!"&CHAR(COLUMN(E5)+64)&ROW())

---

 

[broro183]

Hi,

CkBoy, to answer your question about the indirect function, I've pu
together your attempt with Ioannis', try:

=INDIRECT("'shee
2'!"&LEFT(ADDRESS(1,COLUMN(),4),IF(COLUMN()<27,1,2)&ROW()))

NB: The single apostrphes on each side of the sheet name mean that th
formula will work if the sheet name has spaces in it.
The "sheet 2" can be replaced with the appropriate sheet name or if yo
have a list of sheet names (eg the first one is in cell A2 of the activ
sheet) try:

=INDIRECT("'"&A2&"'!"&LEFT(ADDRESS(1,COLUMN(),4),IF(COLUMN()<27,1,2)&ROW()))


btw, Stefi/Ioannis, yes, you're right it is interesting why the obviou
doesn't come up straight away - Thanks for showing me something new

hth
Rob Brockett
NZ
always learning & the best way to learn is to experience..

 

[Ken Johnson]

Hi broro183,
I was working on this sort of problem only last night using the same
idea of CHAR(COLUMN() + 64). I fiddled around for ages to overcome the
problem of going beyond column Z and came up with
=IF(INT((COLUMN()-1)/26)<1,"",CHAR(64 + INT((COLUMN()-1)/26))) &
CHAR(64 + MOD(COLUMN() + 25,26) +1)
It works but is far too cryptic to actually remember. I later thought
that the ADDRESS function might offer a better solution and came up
with =LEFT(ADDRESS(ROW(),COLUMN(),4),LEN(ADDRESS(ROW(),COLUMN(),4))
-LEN(ROW())) which just extracts the column address characters from the
string returned by the ADDRESS function, which is more straight forward
than the previous formula.
Just thought you might be interested

Ken Johnson

 

[broro183]

Hi Ken,
Thanks, I really like seeing the other options which are available
"outside the square of" my knowledge
You're right, it is cryptic & although I overlooked the use of
"Address", I'd never have even thought of the use of "Int".
I think Ioannis has beaten us both though - IMHO, the use of "If" in
LEFT(ADDRESS(1,COLUMN(),4),IF(COLUMN()<27,1,2) is the tidiest approach
of all.

Cheers
Rob Brockett
NZ
always learning & the best way to learn is to experience...

 

[Ken Johnson]

Hi broro,
I didn't spot Stephi's reply until after replying myself. The Greek
through me off and his reply was hidden under the blue Show quoted
text. It's funny how our thinking sometimes takes us up the garden
path.
Ken Johnson

 

[Ken Johnson]

Hi broro,
a funny thing happened up the garden path!
When the next version of Excel is released I believe it will have 16384
columns. If Microsoft sticks with the alphabetical column headings they
will go up to Column XFD.
Stephi's formula will not cope with the extra letter in the column
headings.
My formula, quite by accident I must admit, will still work!
Ken Johnson

 

[cKBoy]

Awesome suggestions.. thanks a lot. I have tried
=INDIRECT("'Sheet'!"&CHAR(COLUMN()+64)&ROW()) and it works perfectly!
now to try the more complex version for my AA AB columns. Thanks
again guys... you have been very helpful indeed.

 

[hightide]

=IF(INT(COLUMN()/26)=0,"",CHAR(INT((COLUMN()-1)/26)+96))&CHAR(IF(MOD(COLUMN(),26)=0,26,MOD(COLUMN(),26))+96)

Change +96 to +64 for CAPS
Point the column() if you do not want the current row
(i.e. column($A1)