I have an excel column which contains integers and I need to filter the column for values that starts with “5000”. I tried using the normal filter but it’s blank
Hi @O_Prince
Use Filter Data Table activity and you can give the condition as in image in Configure Filter section. Input data table is the data table you get when you read the excel and created a variable under Filtered Data Table section.
Hope it helps!!
I tried thus already but it returns a blank column. The starts with operation doesn’t work on integer values. Please how do i convert the all column values to a string
Try this syntax:
dataTable.AsEnumerable().Where(Function(row) row.Field(Of String)("columnName").StartsWith("5000")).CopyToDataTable()
Use the above syntax in Assign activity and store it in data Table.
Hope it works!!
Hi,
Unfortuntelly, numeric value cannot be filitered by StartsWith inf Filter activity or FilterDataTable activity.
Can you try the following sample using LINQ ?
arrDr=dt.AsEnumerable.Where(Function(r) r("number").ToString.StartsWith("5000")).ToArray()
Sample20230728-1.zip (9.7 KB)
This outputs filterded datatable to “result” sheet.
Regards,
It’s throwing an error saying character not valid
@O_Prince
Under column name give the column name you have and remove the semicolon at last as it,s typing mistake.
Reagrds,
It returned an empty sheet
Please can you create me a sample workflow to resolve this. It’s not working at all
Book1.xlsx (9.8 KB)
This is the dummy sheet. I want to get the values from Column “Numbers” that starts with “533477” and paste into a new sheet.
In this excel column Numbers is based on which column please specify.
Regards,
Column E the column name is Numbers
HI @O_Prince
In Filter Data Table activity provide the condition as below image:
It’s is working at my end.
Output:
Hope it works!!
@O_Prince ,
Use an assign activity and in the right side type the below query
dt_test.AsEnumerable().Where(Function(row) row("Number").ToString().StartsWith("533477")).Select(Function(row) row("Number").ToString()).ToList()
And in left side keep a list variable
Below file is for your reference
testt.zip (10.7 KB)
Hope it helps you!
Or use this Linq Query:
Dt_result= Dt.AsEnumerable().Where(Function(row) row.Field(Of String)("Number").StartsWith("533477")).CopyToDataTable()
Dt_result= datatype: DataTable
Output:
Regards,
Hi,
Can you try the following sample?
arrDr = dt.AsEnumerable.Where(Function(r) r("Number").ToString.StartsWith("533477")).ToArray()
Sample20230728-1v2.zip (18.0 KB)
Regards,
Did it work for you? If yes, please mark it as solution so that it will help other members in the community.
Happy Automation 
Regards
This worked, Thank you. Please what if there are more than one number start with formats for example values that start with both 533477 and 123456. Then it writes them to the new sheet
Hi @O_Prince
You can use this Linq query:
Dt.AsEnumerable().Where(Function(row) row.Field(Of String)("Number").StartsWith("533477") AndAlso row.Field(of String)("Number").StartsWith("123456")).CopyToDataTable()
Hope it works!
sharazkm32
ashokkarale
marquezd
RGTaffy
SorenB
cprima
KB_Fleet
Nithinkrishna
V_M_P_Bharath
Nisha_K21