# Get last column of Excel sheet in Alphanumeric form

I want to write a new column at the end of my excel sheet. I copied the data in a datatable. From that i am able to get the count of columns from totalColumns= DataTable.Columns.Count.
I need the column in alpha numeric form ie AC12 or Z etc instead of number (200 or 26).
There is a package EPPlus that may be helpful.However, i am not able to find the function that will give me the result.

2 Likes

Try this.
Convert.ToChar(dt.Columns.Count + 64)

Itâ€™s vb.net so I did a search for the syntax.

EDIT: Oh and AA-ZZ columns might take additional coding.

agree

their are two ways to do this
One is by using Excel Formula

and other is by mapping

I have used second approach in past and it works

string columnName = String.Empty;
int modulo;

``````while (dividend > 0)
{
modulo = (Column_Number- 1) % 26;
columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
dividend = (int)((Column_Number- modulo) / 26);
}
``````

Regardsâ€¦!!
AKsh

This is what I got for a 1-liner to use in a UiPath field like Range
If(dt.Columns.Count<=26,Convert.ToChar(dt.Columns.Count + 64),Convert.ToChar(CInt(dt.Columns.Count/26) + 64)+Convert.ToChar(If(dt.Columns.Count mod 26 = 0,26,(dt.Columns.Count mod 26)) + 64))

So you use Int(count/26) for first character and (count mod 26) for second character, basically. If mod = 0 then use 26 for Z.

It could be improved.

1 Like

This seems to be promising. Can you please tell where and how to use this code
(Forgive the extra questioning, iâ€™m a rookie )
I tried to put it in a variable value- doesnâ€™t seem to work

So on giving value as 12, 200 etc to variable columns-- i am getting output as â€ś@â€ť

Hey @Als

please find the attached workflow and let me know the feedback on this.
Excel_Column_Name_mapping.xaml (7.2 KB)

Regardsâ€¦!!
Aksh

15 Likes

You ,my friend, are a life saver! It works perfectly!! Thanks

PS- I wanted to get the last column-- but seems my excel sheet has rogue data in it-- Apparently the last free column is AE ie 31. But column count is coming 221

hey @Als,
I figured out what was wrong with my solution. I have added it to @aksh1yadav 's example, so you can check that one out if youâ€™d like also.
Excel_Column_Name_mapping.xaml (8.5 KB)

If you have trouble getting the column number correct, you could do an indexOf of your datatable maybe?
For example,
dt1.Columns.IndexOf(â€ścolumnnameâ€ť)+1

Thanks.

2 Likes

@ClaytonM Hey! Its working correctly now
The problem is that i am not sure what the last header is going to be,it could be blank header for all i know - so need to get the last empty column blindly

@Als Ah. Seems like you would need to loop through each Column, filter it to non-empties, then take a count of the rows found.

For example,
n=-1
Do
n=n+1
While ( dt.AsEnumerable().Where(Function(row) row(n).ToString.Trim<>"").ToArray().Count > 0 and n<dt.Columns.Count-1 )

Untested

Therefore, n would give you the end of the columns. You could also work backwards and initialize n to .Columns.Count with n=n-1 instead of n=n+1

Iâ€™m not a fan of looping through rows and columns of a datatable but thatâ€™s an option.

Thanks.

1 Like