Solved

How to convert a number to an Excel cell address

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

863 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

18 Experts available now in Live!

Get 1:1 Help Now