Solved

Get Excel column name

Posted on 2015-01-13
9
125 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 28

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 28

Expert Comment

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

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 28

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

Suggested Solutions

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
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…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

813 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now