Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Get Excel column name

Posted on 2015-01-13
9
Medium Priority
?
137 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
Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

 
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 2000 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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

598 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