how to get the cell column of a range

Question

I'm iterating over a row which I got from a range in order to find a specific word in the cell content and then I want to get the column where I find it. For example, if I find the desired content at the 19th place, it means the the excel column is "S".

Here is the code I'm using so far:

Excel.Worksheet xlWorkSheet = GetWorkSheet(currentWorkBook, "sheet");
var row = xlWorkSheet.Rows["5"];
int rowLength = xlWorkSheet.UsedRange.Columns.Count;
Excel.Range currentTitle = row[1]; //in order to iterate only over the 5th row in this example
  for (int i = 1; i < rowLength; i++)
  {
    string title = currentTitle.Value2[1,i];
    if (title == null)
    {
      continue;
    }
    if (title.Contains(wordToSearch))
    {
      string column = THIS IS THE QUESTION - WHAT DO I NEED TO WRITE HERE?
      Excel.Range valueCell = xlWorkSheet.Range[column + "5"];
      return valueCell.Value2;
    }

notice the line of string column in which i need to add the code.

@SergeyKucher only if there's no property of the range I don't know about which gives me the column of the cell. It sounds really trivial that there should be this kind of property. — CodeMonkey, Aug 12 '13 at 14:42
Off the top of my head: `string column = ((char)('A' + yourNumber - 1)).ToString();` ... or is the problem to get the `19`? — Corak, Aug 12 '13 at 14:43
@Corak that would work for regular letters. We're talking about excel cells, meaning the 28th place for example is "AB". I'm not limited to only 26 numbers — CodeMonkey, Aug 12 '13 at 14:45
@pnuts it's indeed a solution and I saw it before posting this question but I'm trying to avoid it since I bet there's just a property you can get with the column name — CodeMonkey, Aug 12 '13 at 14:55

score 1 · Accepted Answer · answered Aug 12 '13 at 15:17

As far as you don't want to rely on any calculation, the other option I see is extracting the column letter from Address property, like in the code below:

Excel.Range currentRange = (Excel.Range)currentTitle.Cells[1, i];
string columnLetter = currentRange.get_AddressLocal(true, false, Excel.XlReferenceStyle.xlA1, missing, missing).Split('$')[0];
string title = null;
if (currentRange.Value2 != null)
{
     title = currentRange.Value2.ToString();
}

As you can see, I am forcing a "$" to appear in order to ease the column letter retrieval.

how to get the cell column of a range

1 Answers1