• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 142
  • Last Modified:

Get Excel column name

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
Errol Farro
Asked:
Errol Farro
  • 4
  • 2
  • 2
  • +1
1 Solution
 
Haris DjulicCommented:
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
 
Errol FarroAuthor Commented:
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
 
Haris DjulicCommented:
Oh I see... Missed the ColdFusion tag.
0
Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

 
Pravin AsarPrincipal Systems EngineerCommented:
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
 
Pravin AsarPrincipal Systems EngineerCommented:
My code works from A (1) ---> ZZ (702)
0
 
Pravin AsarPrincipal Systems EngineerCommented:
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
 
_agx_Commented:
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
 
Pravin AsarPrincipal Systems EngineerCommented:
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
 
_agx_Commented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now