| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Column() to return a letter instead of a number?
Can column() return a letter instead of a number? I am planning to use it with INDIRECT? Is that possible? =INDIRECT(row() & column())? -- cKBoy ------------------------------------------------------------------------ cKBoy's Profile: http://www.excelforum.com/member.php...o&userid=31522 View this thread: http://www.excelforum.com/showthread...hreadid=512119 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Column() to return a letter instead of a number?
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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=512119 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Column() to return a letter instead of a number?
Awesome! worked like a charm... Thanks! :) -- cKBoy ------------------------------------------------------------------------ cKBoy's Profile: http://www.excelforum.com/member.php...o&userid=31522 View this thread: http://www.excelforum.com/showthread...hreadid=512119 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Column() to return a letter instead of a number?
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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=512119 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Column() to return a letter instead of a number?
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? -- cKBoy ------------------------------------------------------------------------ cKBoy's Profile: http://www.excelforum.com/member.php...o&userid=31522 View this thread: http://www.excelforum.com/showthread...hreadid=512119 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Column() to return a letter instead of a number?
if column =AA?
so better =LEFT(ADDRESS(1,COLUMN(),4),IF(COLUMN()<27,1,2)) ------- Ioannis, Athens "cKBoy" wrote: Can column() return a letter instead of a number? I am planning to use it with INDIRECT? Is that possible? =INDIRECT(row() & column())? -- cKBoy ------------------------------------------------------------------------ cKBoy's Profile: http://www.excelforum.com/member.php...o&userid=31522 View this thread: http://www.excelforum.com/showthread...hreadid=512119 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Column() to return a letter instead of a number?
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: 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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=512119 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Column() to return a letter instead of a number?
It's interesting why obvious solutions don't come up sometimes!
Stefi ΓΙΑΝΝΗΣ Χ.Β. ezt *rta: if column =AA? so better =LEFT(ADDRESS(1,COLUMN(),4),IF(COLUMN()<27,1,2)) ------- Ioannis, Athens "cKBoy" wrote: Can column() return a letter instead of a number? I am planning to use it with INDIRECT? Is that possible? =INDIRECT(row() & column())? -- cKBoy ------------------------------------------------------------------------ cKBoy's Profile: http://www.excelforum.com/member.php...o&userid=31522 View this thread: http://www.excelforum.com/showthread...hreadid=512119 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Column() to return a letter instead of a number?
try
=INDIRECT("sheet1!"&LEFT(ADDRESS(1,COLUMN(sheet1!E 5),4),IF(COLUMN(sheet1!E5)<27,1,2))&ROW(sheet1!E5) ) ---- Ioannis, Athens "cKBoy" wrote: 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? -- cKBoy ------------------------------------------------------------------------ cKBoy's Profile: http://www.excelforum.com/member.php...o&userid=31522 View this thread: http://www.excelforum.com/showthread...hreadid=512119 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Column() to return a letter instead of a number?
=INDIRECT(Sheet1.CHAR(COLUMN(E5)+64)&ROW())
Try putting it as: =INDIRECT("'Sheet1'!"&CHAR(COLUMN(E5)+64)&ROW()) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Column() to return a letter instead of a number?
Hi, CkBoy, to answer your question about the indirect function, I've put together your attempt with Ioannis', try: =INDIRECT("'sheet 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 the formula will work if the sheet name has spaces in it. The "sheet 2" can be replaced with the appropriate sheet name or if you have a list of sheet names (eg the first one is in cell A2 of the active sheet) try: =INDIRECT("'"&A2&"'!"&LEFT(ADDRESS(1,COLUMN(),4),I F(COLUMN()<27,1,2)&ROW())) btw, Stefi/Ioannis, yes, you're right it is interesting why the obvious 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... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=512119 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Column() to return a letter instead of a number?
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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Column() to return a letter instead of a number?
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 Wrote: 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 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=512119 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Column() to return a letter instead of a number?
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 |
|
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Column() to return a letter instead of a number?
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 |
|
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Column() to return a letter instead of a number?
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. :) -- cKBoy ------------------------------------------------------------------------ cKBoy's Profile: http://www.excelforum.com/member.php...o&userid=31522 View this thread: http://www.excelforum.com/showthread...hreadid=512119 |
|
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Column() to return a letter instead of a number?
=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) On Thu, 16 Feb 2006 21:15:19 -0600, cKBoy wrote: 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. :) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
| Running total w/2 columns - Excel | Excel Worksheet Functions | |||
| How do I change column label from number to letter? | Excel Discussion (Misc queries) | |||
| return the column reference number of a function result | Excel Discussion (Misc queries) | |||
| Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Linear Mode
