Getting column Letter from Value in header

Hi, I want to get the column letter based on the value in the column header.

I have the below which works up to column Z. Then after that when the columns change to AA, AB etc it fails. Any idea how to fix this?

Convert.ToChar(ExtractDataTable.Columns.IndexOf(myVariable)+65).ToString

Hi @fflattery ,

Use below code in your invoke code:

Dim dividend As Integer = columnNumber
Dim modulo As Integer

While dividend > 0
   modulo = (dividend - 1) Mod 26
   columnName = Convert.ToChar(65 + modulo).ToString() & columnName
   dividend = CInt((dividend - modulo) / 26)

End While

Note: Use argument to pass in invoke code shown below:

1 Like

What is the index being declared?

I want to be able to get the column letter based on the contents in row 1. so if its ADAI i want to get column C

image

Hi @fflattery ,

Here index is the column number, if you just pass the column number it will convert it into Excel column( In Alphabets).
But i got, you want it on the basis of value in the excel. Let me do something on that also.

Thanks

1 Like

@fflattery

You can use For Each activity and write as Dt.Columns
Declare a variable for For Each Index also
Use If Condition as Item = ADAI and then you can place the invoke code function given by Manoj

Hope this may help you

Thanks

1 Like

Hi @fflattery ,

Try this to get index number of column.

Will this code give me the column letters past Z? what if i have like the below?

image

Yes, you can use it.
Test.xaml (5.0 KB)

@fflattery - You can use this logic…This will cover from “A to AZ”…

image

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.