Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output thecorresponding column text label
in A1
=column(D2) would return the number 5. I want the reverse of that. e.g.a function that would when pointed at A1, returns the text "D" This beast does the job, but is there are more elegant way? =LEFT((ADDRESS(1,A1,2)),((FIND("$",(ADDRESS(1,A1,2 ))))-1)) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output the corresponding column text label
I doubt if anyone would call this more elegant... hell, it probably isn't
even as efficient... just different. =REPLACE(ADDRESS(1,A1,2),FIND("$",ADDRESS(1,A1,2)) ,9,"") -- Rick (MVP - Excel) "Brotherharry" wrote in message ... in A1 =column(D2) would return the number 5. I want the reverse of that. e.g.a function that would when pointed at A1, returns the text "D" This beast does the job, but is there are more elegant way? =LEFT((ADDRESS(1,A1,2)),((FIND("$",(ADDRESS(1,A1,2 ))))-1)) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output thecorresponding column text label
Brotherharry wrote:
in A1 =column(D2) would return the number 5. I want the reverse of that. e.g.a function that would when pointed at A1, returns the text "D" This beast does the job, but is there are more elegant way? =LEFT((ADDRESS(1,A1,2)),((FIND("$",(ADDRESS(1,A1,2 ))))-1)) More elegant? In this context, elegant is defined as "gracefully concise and simple; admirably succinct." So, subjective. How about this? =MID(ADDRESS(1,A1),2,FIND("$",ADDRESS(1,A1),2)-2) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output the corresponding column text label
If your numbers are always less than 27, then a simple solution is
=CHAR(A1+64) Otherwise, this same approach gets more complicated (for columns above Z) =IF(A126,CHAR(INT(A1/26)+64),"")&CHAR(MOD(A1,26)+64) HTH, Bernie MS Excel MVP "Brotherharry" wrote in message ... in A1 =column(D2) would return the number 5. I want the reverse of that. e.g.a function that would when pointed at A1, returns the text "D" This beast does the job, but is there are more elegant way? =LEFT((ADDRESS(1,A1,2)),((FIND("$",(ADDRESS(1,A1,2 ))))-1)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output the corresponding column text label
Here is yet another way...
=IF(A126,CHAR(64+INT(A1/26)),"")&CHAR(65+MOD(A1-1,26)) -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... I doubt if anyone would call this more elegant... hell, it probably isn't even as efficient... just different. =REPLACE(ADDRESS(1,A1,2),FIND("$",ADDRESS(1,A1,2)) ,9,"") -- Rick (MVP - Excel) "Brotherharry" wrote in message ... in A1 =column(D2) would return the number 5. I want the reverse of that. e.g.a function that would when pointed at A1, returns the text "D" This beast does the job, but is there are more elegant way? =LEFT((ADDRESS(1,A1,2)),((FIND("$",(ADDRESS(1,A1,2 ))))-1)) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output the corresponding column text label
Otherwise, this same approach gets more complicated (for columns above Z)
=IF(A126,CHAR(INT(A1/26)+64),"")&CHAR(MOD(A1,26)+64) This one fails for a value of 26 (it returns "@" instead of "Z"). I ran into the same problem while developing my 2nd posted solution. -- Rick (MVP - Excel) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output the corresponding column text label
Otherwise, this same approach gets more complicated (for columns above Z)
=IF(A126,CHAR(INT(A1/26)+64),"")&CHAR(MOD(A1,26)+64) This one fails for a value of 26 (it returns "@" instead of "Z"). I ran into the same problem while developing my 2nd posted solution. I should have mentioned here, the fix is simple... change the part after the ampersand to this... CHAR(MOD(A1-1,26)+65) Rick |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output the co
Hi,
How do you get 5 from COLUMN(D)? -- If this helps, please click the Yes button Cheers, Shane Devenshire "Brotherharry" wrote: in A1 =column(D2) would return the number 5. I want the reverse of that. e.g.a function that would when pointed at A1, returns the text "D" This beast does the job, but is there are more elegant way? =LEFT((ADDRESS(1,A1,2)),((FIND("$",(ADDRESS(1,A1,2 ))))-1)) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output the corresponding column text label
On Fri, 13 Feb 2009 12:54:29 -0500, "Rick Rothstein"
wrote: Here is yet another way... =IF(A126,CHAR(64+INT(A1/26)),"")&CHAR(65+MOD(A1-1,26)) For A1=52 this gives BZ instead of AZ For A1=78 this gives CZ instead of BZ and so on And if you have Excel 2007, the formula does not work for A1702 Lars-Åke |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output the corresponding column text label
Actually, as Lars-Åke points out in my sub thread, this fix does not really
work. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Otherwise, this same approach gets more complicated (for columns above Z) =IF(A126,CHAR(INT(A1/26)+64),"")&CHAR(MOD(A1,26)+64) This one fails for a value of 26 (it returns "@" instead of "Z"). I ran into the same problem while developing my 2nd posted solution. I should have mentioned here, the fix is simple... change the part after the ampersand to this... CHAR(MOD(A1-1,26)+65) Rick |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output the corresponding column text label
Good catch! Thanks for point it out.
-- Rick (MVP - Excel) "Lars-Åke Aspelin" wrote in message ... On Fri, 13 Feb 2009 12:54:29 -0500, "Rick Rothstein" wrote: Here is yet another way... =IF(A126,CHAR(64+INT(A1/26)),"")&CHAR(65+MOD(A1-1,26)) For A1=52 this gives BZ instead of AZ For A1=78 this gives CZ instead of BZ and so on And if you have Excel 2007, the formula does not work for A1702 Lars-Åke |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output the corresponding column text label
Okay, this should fix the problem for XL2003 and earlier...
=IF(A126,CHAR(64+INT((A1-1)/26)),"")&CHAR(65+MOD(A1-1,26)) -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Good catch! Thanks for point it out. -- Rick (MVP - Excel) "Lars-Åke Aspelin" wrote in message ... On Fri, 13 Feb 2009 12:54:29 -0500, "Rick Rothstein" wrote: Here is yet another way... =IF(A126,CHAR(64+INT(A1/26)),"")&CHAR(65+MOD(A1-1,26)) For A1=52 this gives BZ instead of AZ For A1=78 this gives CZ instead of BZ and so on And if you have Excel 2007, the formula does not work for A1702 Lars-Åke |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output the corresponding column text label
Well, the fix I gave you is correct for the part of your formula it applies
to. What Lars-Åke pointed out was a problem with the IF part of our code. Here is the corrected formula I posted in my sub thread... =IF(A126,CHAR(64+INT((A1-1)/26)),"")&CHAR(65+MOD(A1-1,26)) I'm pretty sure this is correct (for XL2003 and earlier as Lars-Åke also pointed out). -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Actually, as Lars-Åke points out in my sub thread, this fix does not really work. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Otherwise, this same approach gets more complicated (for columns above Z) =IF(A126,CHAR(INT(A1/26)+64),"")&CHAR(MOD(A1,26)+64) This one fails for a value of 26 (it returns "@" instead of "Z"). I ran into the same problem while developing my 2nd posted solution. I should have mentioned here, the fix is simple... change the part after the ampersand to this... CHAR(MOD(A1-1,26)+65) Rick |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output the co
Hi,
One way =MID(ADDRESS(1,A1),2,FIND("$",ADDRESS(1,A1,2))-1) or a modification, but slightly longer version, of a previous suggestion =LEFT(ADDRESS(1,A1,2),FIND("$",ADDRESS(1,A1,2))-1) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Brotherharry" wrote: in A1 =column(D2) would return the number 5. I want the reverse of that. e.g.a function that would when pointed at A1, returns the text "D" This beast does the job, but is there are more elegant way? =LEFT((ADDRESS(1,A1,2)),((FIND("$",(ADDRESS(1,A1,2 ))))-1)) |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output the co
Hi,
and here is an even shorter method: =LEFT(ADDRESS(1,A1,2),LEN(ADDRESS(1,A1))-3) down from =MID(ADDRESS(1,A1),2,FIND("$",ADDRESS(1,A1,2))-1) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Brotherharry" wrote: in A1 =column(D2) would return the number 5. I want the reverse of that. e.g.a function that would when pointed at A1, returns the text "D" This beast does the job, but is there are more elegant way? =LEFT((ADDRESS(1,A1,2)),((FIND("$",(ADDRESS(1,A1,2 ))))-1)) |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output the co
Hi,
And to take the last simplification and go further: from =LEFT(ADDRESS(1,A1,2),LEN(ADDRESS(1,A1))-3) to 2007: =LEFT(ADDRESS(1,A1,2),1+(A126)+(A1702)) 2003: =LEFT(ADDRESS(1,A1,2),1+(A126)) -- If this helps, please click the Yes button Cheers, Shane Devenshire "Brotherharry" wrote: in A1 =column(D2) would return the number 5. I want the reverse of that. e.g.a function that would when pointed at A1, returns the text "D" This beast does the job, but is there are more elegant way? =LEFT((ADDRESS(1,A1,2)),((FIND("$",(ADDRESS(1,A1,2 ))))-1)) |
#17
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output the co
2007:
=LEFT(ADDRESS(1,A1,2),1+(A126)+(A1702)) 2003: =LEFT(ADDRESS(1,A1,2),1+(A126)) Nice going Shane... I would definitely call these formulas the "elegant" ones the OP was looking for. Rick |
#18
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output theco
Rick Rothstein wrote:
2007: =LEFT(ADDRESS(1,A1,2),1+(A126)+(A1702)) 2003: =LEFT(ADDRESS(1,A1,2),1+(A126)) Nice going Shane... I would definitely call these formulas the "elegant" ones the OP was looking for. Rick Agreed! |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output th
Thanks Guys!!!
Cheers, Shane Devenshire "Glenn" wrote: Rick Rothstein wrote: 2007: =LEFT(ADDRESS(1,A1,2),1+(A126)+(A1702)) 2003: =LEFT(ADDRESS(1,A1,2),1+(A126)) Nice going Shane... I would definitely call these formulas the "elegant" ones the OP was looking for. Rick Agreed! |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
inverse of the column function? i.e. input a number, output theco
Shane Devenshire wrote...
And to take the last simplification and go further: from =LEFT(ADDRESS(1,A1,2),LEN(ADDRESS(1,A1))-3) to 2007: =LEFT(ADDRESS(1,A1,2),1+(A126)+(A1702)) 2003: =LEFT(ADDRESS(1,A1,2),1+(A126)) .... You could use the 2007 formula in 2003. As long as A1 contains a valid column number (between 1 and 256), the (A1702) test will be FALSE. No good reason to use different formulas in different Excel versions when it isn't necessary. Then again, if some future Excel versions goes beyond columns ZZZ, as long as the ADDRESS function remains unchanged, it'd be more robust to use =SUBSTITUTE(ADDRESS(1,A1,4),"1","") This ain't rocket science. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Output in Column A based on Input in Column B | Excel Worksheet Functions | |||
How do I change column label from number to letter? | Excel Discussion (Misc queries) | |||
chart label reference based on the column number | Charts and Charting in Excel | |||
chart label reference based on the column number | Charts and Charting in Excel |