Google-sheets – Using FILTER or IMPORTRANGE to bring in and filter data from another spreadsheet with a keyword instead of exact match

google sheets

I would like to use the importrange formula to import data from "spreadsheetA" that contains "keyword" in columnD.

I can get importrange to work but I cannot figure out how to filter it so it will only import if a keyword is found in column d.

Best Answer

You nest an import range to a query to meet your desired result:

=query(IMPORTRANGE("key","Sheet1!A:C"),"Select * Where Col2 contains 'Keyword'")

Demo sheet for you:

https://docs.google.com/spreadsheets/d/1xT3kK4XGNGuAkMxSN2OvYmCgHn_tMsbbd4OjtwnmHRI/edit#gid=0

If you get a REF error you need to give yourself or get permission from the owner to read data from that sheet.

Related Solutions

Google Sheets – Using FILTER or IMPORTRANGE to Filter Data from Another Spreadsheet

Use query instead of filter for this:

=query(importrange("URL","A:J"), "select * where Col3 = 'Yes'")

The command query can refer to the columns of the array that it receives in an abstract way: Col1, Col2, and so forth, without being tied to their placement within the sheet. So, it filters the thing "in the cloud" and then puts the result into the sheet.

In contrast, filter can only refer to columns by the cell names like C:C. This means that the array must first be placed into the sheet, and then be filtered in place according to its own values. This runs into circular dependency.

Google-sheets – Google Sheets LEFT function trim text generated by IMPORTHTML function

Short Answer

Instead of LEFT() use RIGHT().

Explanation

The LEFT() function returns the leftmost characters, but the OP want to get the rightmost characters.

The following is a simplified version of the final formula, for explanatory purposes:

=RIGHT("Sector: Industrial Goods",16)

The following is the final formula; in order to improve readability, each parameter is inserted in a new line:

 =RIGHT(
   iferror(
     INDEX(
       IMPORTHTML(
         "http://www.thestreet.com/quote/GE/details/company-profile.html",
          "table",
          3
        ),
        8,
        1
     )
   ),
   16
 )