| Home |
| Search |
| Today's Posts |
|
|
|
#1
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 |
|
#2
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 |
|
#3
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 |
| 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 | |||
![Lars-Åke Aspelin[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
