Solved

Get Excel column name

Posted on 2015-01-13
9
132 Views
Last Modified: 2015-01-13
I am looking for a function to display the standard column name of an Excel after entering the column number.

Example
1 ---> A
8 ---> H
26 ---> Z
31 ---> AE
95 ---> CQ
387 ---> NW
1088 ---> AOV
0
Comment
Question by:Errol Farro
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40546505
Hi,

VBA code for this should be something like this:

Split(Cells(1, col_num).Address(True, False), "$")

Open in new window


where col_num is number of column
0
 

Author Comment

by:Errol Farro
ID: 40546592
Hi Haris,

Thanks for your reply.

Actually I need a Coldfusion code whereby I submit the column number and the function returns the column name.
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40546635
Oh I see... Missed the ColdFusion tag.
0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
LVL 29

Expert Comment

by:Pravin Asar
ID: 40547203
Hope this works for you. It is a matter doing little math


<cfscript>
function Num2Letter(iCol)  {
   iAlpha = Int(iCol / 27);
   iRemainder = iCol - (iAlpha * 26);
   ConvertToLetter="";
   if (iAlpha > 0) {
      ConvertToLetter = Chr(iAlpha + 64);           
   }
   if (iRemainder > 0) {
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64);           
   }
   return ConvertToLetter;
}
</cfscript>
<cfoutput>
<br/>#Num2Letter(1)#
<br/>#Num2Letter(8)#
<br/>#Num2Letter(27)#
</cfoutput>
0
 
LVL 29

Expert Comment

by:Pravin Asar
ID: 40547215
My code works from A (1) ---> ZZ (702)
0
 
LVL 29

Expert Comment

by:Pravin Asar
ID: 40547295
Here is updated code.

<cfscript>
function Num2Letter(iCol)  {
   local.iAlpha = Int(iCol / 27);
   local.iRemainder = iCol - (iAlpha * 26);
   local.ConvertToLetter="";
   if (local.iAlpha > 26) {
              local.saveiRemainder= local.iRemainder;
               local.ConvertToLetter = Num2Letter(Int(iCol/26/26)) & Num2Letter (Int((iCol-26*26)/26));
               if (local.saveiRemainder > 26) {
                     local.ConvertToLetter = local.ConvertToLetter & Num2Letter(local.saveiRemainder-26);
               }
               return local.ConvertToLetter;
   }
   if (local.iAlpha > 0) {
      local.ConvertToLetter = local.ConvertToLetter & Chr(local.iAlpha + 64);           
   }
   if (local.iRemainder > 0) {
      local.ConvertToLetter = local.ConvertToLetter & Chr(local.iRemainder + 64);           
   }
   return local.ConvertToLetter;
}
</cfscript>
<cfset values = "1,8,26,31,95,387,1088"/>
<cfoutput>
<cfloop index="value"  list="#values#">
      <br/>#value# ===>  #Num2Letter(value)#
</cfloop>
<br/>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40547347
Rather than rolling your own, you can also use one of the POI utility classes, built into CF:

Utility:
<cfset util = createObject("java", "org.apache.poi.ss.util.CellReference")>

Test Code:
<cfdump var="#util.convertNumToColString(1)#" label="Expected Result 1->A">
<cfdump var="#util.convertNumToColString(8)#" label="Expected Result 8->H">
<cfdump var="#util.convertNumToColString(26)#" label="Expected Result 26->Z">
<cfdump var="#util.convertNumToColString(31)#" label="Expected Result 31->AE">
<cfdump var="#util.convertNumToColString(95)#" label="Expected Result 95->CQ">
<cfdump var="#util.convertNumToColString(387)#" label="Expected Result 387->NW">
<cfdump var="#util.convertNumToColString(1088)#" label="Expected Result 1088->AOV">
0
 
LVL 29

Accepted Solution

by:
Pravin Asar earned 500 total points
ID: 40547485
I know, poi can be used for CF9+

Here is optimized code. Very simple.  
Excel permits 16384 columns  (A through XFD)

<cfscript>
function getExcelColumnName(number) {
        sb = "";
        num = number - 1;
        while (num >=  0) {
            numChar = (num % 26)  + 65;
            sb = #Chr(numChar)# & sb;
            num = (num  / 26) - 1;
        }
        return sb;
    }
</cfscript>
<cfset values = "1,8,26,31,95,387,1088,16384"/>
<cfoutput>
<cfloop index="value"  list="#values#">
      <br/>#value#  ===> #getExcelColumnName(value)#
</cfloop>
<br/>
</cfoutput>
0
 
LVL 52

Expert Comment

by:_agx_
ID: 40547603
Ooops, I pasted the wrong version. Forgot to mention that like most stuff in java, the POI column indexes are 0 based, not 1:
Utility:
<cfset util = createObject("java", "org.apache.poi.ss.util.CellReference")>

Test Code:
<cfset cols = [1,8,26,31,95,387,1088]>
<cfloop array="#cols#" index="index">
      <cfdump var="#util.convertNumToColString( index-1 )#" label="Column #index#"><br>
</cfloop>
<cfabort>
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question