Filter column on 'value starts with ...'


#1

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!


#2

@AnniekJ, Refer this

Regards,
Dom :slight_smile:


#3

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

exampleFile.xaml (6.5 KB)


#4

@AnniekJ, Possibly change your datatype as follows,

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

Regards,
Dom :slight_smile:


#5

That works perfectly, thanks a lot!


#6

use this one
exampleFile.xaml (7.6 KB)

Regards,
Arivu


#7