yogi_Querying Fields With Mixed Data Type -- Be it Numeric Text or Blank

Yogi Anand, D.Eng, P.E.                                         Google Spreadsheet                            www.energyefficientbuild.com

Querying data from fields with mixed type data

can become a daunting task -- especially in regard to

what is considered null in dealing with different data types

formula for extracting rows where Col2 is Blank:

=query(index(SourceData!A:C&""),"select* where Col2 ='' ",1)

formula for extracting rows where Col2 is NotBlank:

=query(index(SourceData!A:C&""),"select* where Col2 <>'' ",1)

formula (in row2 -- start of data) for extracting rows where Col2 has NumericData:

=filter(query(SourceData!A2:C,"select* where B!='' ",0),isnumber(index(query(SourceData!A2:C,"select* where B!='' ",0),0,2)+0))

formula for extracting rows where Col2 has TextData:

=query(index(if(isnumber(SourceData!A:C),"",SourceData!A:C)),"select* where Col2<>'' ",1)