How to get column value from cell coordinates?

Help Studio
, , , ,
1

Hello everyone,

I’m currently working on a project where I need to format every cell from a specific column within an excel file, and I know the column’s header value, but it’s index might change between iterations, so I use the find/replace activity on the header and obtain a cell’s coordinates. However, from here, I don’t know how to only get the name specific to that column (for example “C” or “AG”). Is there some way to do it simply, like a “TargetCell.columnName” property for example, or should I just find some way of removing the numbers from the cell’s coordinates ?

Thank you in advance,
Bastien

2

Hi @Bastien_B

I think you can use linq operation with regex to check the column C/AG from it;

yourCellList.Select(Function(c) Regex.Match(c, “^ [A-Z]+”).Value).ToList()

3

@Bastien_B,

Use this expression in assign activity to get only characters from the alphanumeric cell address.

strColumnName=System.Text.RegularExpressions.Regex.Replace(strCellAddress, "[^A-Z]", "")

You will get the column name in strColumnName variable

4

Thank you for the advice, but I’m not getting a list, the function find gets me the coordinates of a cell, and from here I just want to get the column so that I can use it as a range to format all the cells within, it’d avoid having to use a for each on the list after…

5

Thank you for your answer !
On a side note, I guess there is no attributes on excel cells that would allow to get it through specific methods other than regex then ?

6

That’s right @Bastien_B!

7

Convert.ToChar(dt1.Columns.IndexOf(Cell_Header_Value)+65).ToString

Use the above formula in an assign statement. It will give you the column index in alphabet.

Please mark this as solution if this solves your issue. Thanks!