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
Solved

How to convert a number to an Excel cell address

Posted on 2009-05-04
3
688 Views
Last Modified: 2012-06-21
Hello,

I'm using C# and the Excel Interop.  I am reading several different spreadsheets to combine data into one.  I have variable numbers of rows and columns and I need a way to take a column number like 28 and turn that into the Excel cell address of AB as an example.  Any help would be appreciated.

Thank you,
Rob
0
Comment
Question by:rkellow
3 Comments
 
LVL 13

Accepted Solution

by:
usachrisk1983 earned 500 total points
ID: 24301069
You know, you'd think Excel would have this built-in but I looked everywhere for it and it's no where in any of the docs, everyone is hacking around it by getting the character manually (which is how I've always had to do it in VBA).  The code below is from http://www.freevbcode.com/ShowCode.asp?ID=4303

Modify for how you'll need it.

FWIW, you can use the column number when referencing a cell using Excel's Automation, you don't need to use the letter equivalents.  


  If ColumnNumber > 26 Then
 
    ' 1st character:  Subtract 1 to map the characters to 0-25,
    '                 but you don't have to remap back to 1-26
    '                 after the 'Int' operation since columns
    '                 1-26 have no prefix letter
 
    ' 2nd character:  Subtract 1 to map the characters to 0-25,
    '                 but then must remap back to 1-26 after
    '                 the 'Mod' operation by adding 1 back in
    '                 (included in the '65')
 
    ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
                   Chr(((ColumnNumber - 1) Mod 26) + 65)
  Else
    ' Columns A-Z
    ColumnLetter = Chr(ColumnNumber + 64)
  End If

Open in new window

0
 
LVL 4

Expert Comment

by:r0bertdenir0
ID: 24305665
You didn't post any code so I can't tell what you're using this for.
But in general you don't need to use the address of a cell because every worksheet & range has a Cells method that retrieves a cell using Row, Column notation.
If you want the address of any cell, you shud be able to use the same Cells method to retrieve that cell, then call it's Address method which will return it's address in any format you need.

0
 

Author Closing Comment

by:rkellow
ID: 31578194
Hi usachrisk1983,

Thank you for the solution.  I completely agree that this should be something that provided as a method within the Interop but apparently they want to have us do a little work on our own.  I saw some responses that said I didn't need this that I could use the Cells object which I do use but for working with Ranges I couldn't find a way around the alpha cell addressing.

rkellow

Here is the code in C#:

String convertNumberToCellAddress(int iColumnNumber)
        {
            String strCellAddress = "";

            if(iColumnNumber > 26)
            {
                // get the cell address for a cell past cell "Z"
                strCellAddress = Convert.ToString((Char)(((int)(iColumnNumber - 1) / 26) + 64)) +
                    Convert.ToString((Char)(((iColumnNumber - 1) % 26) + 65));
            }
            else
            {
                strCellAddress = Convert.ToString((Char)(iColumnNumber + 64));
            }

            return strCellAddress;
        }
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

840 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