Solved

Get Excel column name

Posted on 2015-01-13
9
123 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
Comment Utility
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
Comment Utility
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
Comment Utility
Oh I see... Missed the ColdFusion tag.
0
 
LVL 28

Expert Comment

by:Pravin Asar
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 28

Expert Comment

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

Expert Comment

by:Pravin Asar
Comment Utility
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_
Comment Utility
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
Comment Utility
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_
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 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