[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
Medium Priority
708 Views
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
Question by:rkellow

LVL 13

Accepted Solution

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
``````
0

LVL 4

Expert Comment

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

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#:

{

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
{
}

}
0

## Featured Post

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…