Solved

How to convert a number to an Excel cell address

Posted on 2009-05-04
3
692 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
[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
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

739 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