Extract data from a excel by applying special text filter 'Begins With' and extract it to another excel file

excel
studio

#1

Hello I want to filter a data from excel by applying a filter that would help me to find those rows which begins with a specified string variable. for example see my attached excel file.
Testbook for filter.xlsx (10.2 KB)

here from sheet if i want to filter the data that begins with “b2/” then it should give me the rows which begins with “b2/” and copy that data and paste it in another sheet.

here the value “b2/” comes from a string variable. Please refer the sheet “Filtered Data” for expected output ans “Sheet1” is the Raw Data.


#2

@uppatel check attached file ExlRowFilter.zip (10.1 KB)


#3

Hello @indra,

Thankyou so much for your quick help. Really your solution helped me a lot.


#4

hello @indra

I have one doubt in this. How to pass variable instead of “b%” in below syntax

“dt.Select(“col_two like ‘b%’”).ToList()”

means searching rows depends on a variable. if variable has value “b/” then it should filter rows starting with “b/”. and if value of variable is “e/” then it should filter rows starting with “e/”. and so.


#5

@uppatel dt.Select(“col_two like ‘e/%’”).ToList()


#6

@indra,
Thanks for your solution,

i have understood that but believe that the rows to be sorted is obtained from a variable.

Means believe “rowsToFilter” is a variable and its vale changes every time.

so if rowsToFilter = b/ then it would filter rows starting with b/.
if rowsToFilter = e/ then it would filter rows starting with e/.

and another doubt i have that can i pass multiple value to that variable.
Means if i want to filter all rows starting with b/,e/ and a/. then how should be the syntax.

Please help me to solve this logic.


#7

@uppatel you can pass variable like this dt.Select(“col_two like '”+variable1+"’ ").ToList


#8

Hello,

Exactly my requirement for this case is that , I get a folder name to be filtered in report extracted to excel and that also in a single cell. like below

https://testsite.com/sites/testfolder/Shared Documents/b
https://testsite.com/sites/testfolder/Shared Documents/c/c1/c2
https://testsite.com/sites/testfolder/Shared Documents/e/e2

from this i have to separate the folder name like (1) b, (2) c/c1/c2, (3) e/e2, and filter the rows from excel file which begins with the above separated name, and write them in a separate excel file.

Please see the attached excel file. The sheet 1 has paths obtained, sheet 2 contains the file where filter is needed to apply. and sheet 3 contains the filtered result with the rows starting with above folder name.
Testbook for filter.xlsx (12.1 KB)

Please Help me to solve this logic. I am in urgent need of this solution.


#9

All the 3 URLs are in a single cell of excel? Or they would be in a separate row one for each URL?


#10

hello @prankurjoshi
all the 3 urls are in the single cell of excel.