Filter column on 'value starts with ...'

[AnniekJ (Anniek)]

Hi all,

Hope you can help me.
I want to filter a table in excel on all numbers that start with a certain number.
For instance, in the example file attached, I want to filter out all people who’s age starts with the number 3.

Book2.xlsx (10.2 KB)

I tried using the ‘Filter table’ activity, but you need the actual value you want to filter on. Second idea was doing a ‘Read range’ on the specific column, then a ‘For each’, in which I add every value from the column that starts with 3 to a list collection, and then doing the ‘Filter table’ activity, with as filter the list collection created before. But with this idea I run into some problems with the list collection, since I don’t fully understand how it works.

Hope somebody can help me.

Thanks in advance!

[Dominic]

@AnniekJ, Refer this

Create table from document and return value if found

Hi @andnesper, You can use below code to get only column value start with 4. Dt=Dt.Select("ColumnName like '4℅'"). CopyToDataTable() If you want to count of the column name start with 4 then use below code. IntCount=Dt.Select("ColumnName like '4℅'").length Regards, Arivu

Regards,
Dom

[AnniekJ (Anniek)]

Thanks! But with that I get the error “Cannot perform ‘Like’ operation on system.double and system.string”

exampleFile.xaml (6.5 KB)

[Dominic]

@AnniekJ, Possibly change your datatype as follows,

Dt=Dt.Select("Convert(ColumnName,System.String) like '3℅'"). CopyToDataTable()

Regards,
Dom

[AnniekJ (Anniek)]

That works perfectly, thanks a lot!

[arivu96 (Arivazhagan A)]

use this one
exampleFile.xaml (7.6 KB)

Regards,
Arivu