How to filter datatable based on column headings that contains specific string?

I have a datatable with column headings.

I need to filter the datatable based on column heading that contains specific string and retrieve the values available in that particular column.

you can start to explore the filter datatable activity and on the column tab it can be configured to keep only a particular column - result: datatable

we can use YourDataTableVar.DefaultView.ToTable(false, “{YourColName}”) - result: datatable with only the configured column

As it is unclear which resulting datatype is needed it could also the case that you are interested on a list/array of the values

arrValues = YourDataTableVar.AsEnumerable(Function (x) x(YourColNameOrIndex).toString).toArray

we can modify the result on datatype (list, array) and returned item datatype (string, int…) as needed

My column heading index varies dynamically. For example, (refer screenshot)
aa12 in column index 0 will be as aa34 in column index 3 tomorrow.
So I need to search each column heading that contains “aa” and get values in that column


  1. First find out the column index of the required column as below.

  2. Initialise index with 0.

        For Each item in dtInput.Columns
              If(item.Contains("required value")
              Then take index value
               Else increment index value by 1.
  3. And then follow the steps mentioned by @ppr and pass index value to it.

the index we can retrieve by following:
colIDX = yourDataTableVar.Columns.Cast(Of DataColumn).toList.FindIndex(Function (x) x.ColumnName.StartsWith(“aa”))

Thanks @lakshman @ppr
Lemme check it out

the column name or Null if not present we can get by:

colName = yourDataTableVar.Columns.Cast(Of DataColumn).Where(Function (x) x.ColumnName.StartsWith(“aa”)).FirstOrDefault()

with the understanding that in a datable we can get many aaXX cols and we want to get all aaXX col names we can do

arrColNames = 

(From c in yourDataTableVar.Columns.Cast(Of DataColumn)
Where c.ColumnName.StartsWith("aa")
Select c.ColumnName).toArray

That’s not the case.

If column heading name contains “aa”, I need to retrieve all the values available in that particular column.

we were talking about the part where we want to calculate the detail column name. Value retrieval was already discussed above

sound like the case, that more then one aaXX columns like aa12, aa34 can be present in the datatable. And on this case was the post aligned. Otherswise refine requirements in a clear, sharp format including all expected variations as well.

There won’t be multiple column name starting with “aa”.

In my datatable, the character followed by “aa” can change.

As per above screenshot, please help in retrieving values 1,4,7 by filtering column heading that contains “aa”.

every building part was already given above. You can implement it

this is why clear req descriptions are needed. Above statement can talk about 1 or multiple columns starting with aa.

However you can implement. Let us know the result. Thanks

Read your datatable, which I assume will be in xlsx? If so, uncheck Add Column Headings. Your first column will always, then, be Column1, etc. If you will always be filtering on the same column? then in your filter datatable activity instead of the column header name put “Column1”, etc

Hope that helps



Let me re-iterate, if i get your statement right (w.r.t data table).
Today, you datatable will look like below

Tomorrow, it will look like below.


If this understanding is correct, let me know which column you want to extract (Index- 0 or 3).

This actually worked. But if no columns found with Column heading that contains “aa”, then the default value “0” is assigned.

So the column index 0 values are displayed in output.


we can handle this with findindex output of -1 when it is not found:

1 Like

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