[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How to convert a number to an Excel cell address

Posted on 2009-05-04
3
Medium Priority
?
708 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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

834 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