Blank Values in FILTER function results

%3CLINGO-SUB%20id%3D%22lingo-sub-2383698%22%20slang%3D%22en-US%22%3EBlank%20Values%20in%20FILTER%20function%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2383698%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20running%20a%20FILTER%20function%20to%20create%20the%20input%20data%20for%20a%20Word%20mail%20merge%20document.%20What%20value%20do%20I%20test%20for%20in%20the%20mail%20merge%20for%20a%20blank%20cell%20returned%20by%20the%20FILTER%20function%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20add%20two%20fields%20into%20the%20mail%20merge%20document%20and%20one%20of%20them%20is%20blank%20in%20the%20source%20data%20the%20mail%20merge%20field%20displays%20a%20zero.%20If%20I%20do%20a%20conditional%20test%20(IF%20THEN%20ELSE)%20in%20the%20mail%20merge%20on%20the%20source%20data%20using%20blank%20(%22%20%22)%20or%20zero%20as%20the%20test%2C%20neither%20find%20the%20blank%20value.%20I'm%20assuming%20that%2C%20behind%20the%20scenes%2C%20Excel%20is%20populating%20a%20%22blank%22%20cell%20result%20from%20a%20FILTER%20function%20as%20some%20special%20character%20or%20value%20(not%20blank%20or%20zero)%20and%20I%20just%20need%20to%20test%20for%20that.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20run%20the%20mail%20merge%20on%20the%20original%20(pre-FILTER)%20data%20it%20picks%20up%20the%20blank%20values%20correctly%20so%20I%20assume%20it's%20something%20about%20the%20way%20data%20is%20treated%20when%20processed%20by%20the%20FILTER%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnyone%20any%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2383698%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2383990%22%20slang%3D%22en-US%22%3ERe%3A%20Blank%20Values%20in%20FILTER%20function%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2383990%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1061558%22%20target%3D%22_blank%22%3E%40rhjtwo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFILTER()%20returns%20blanks%20as%20zero.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2384415%22%20slang%3D%22en-US%22%3ERe%3A%20Blank%20Values%20in%20FILTER%20function%20results%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2384415%22%20slang%3D%22en-US%22%3EThanks%20Sergei%2C%3CBR%20%2F%3E%3CBR%20%2F%3EIt%20certainly%20appears%20to.%20When%20I%20print%20the%20value%20in%20mail%20merge%20it%20prints%20as%20zero.%20My%20issue%20is%20that%20if%20I%20test%20for%20the%20zero%20value%20in%20a%20conditional%20IF%20THEN%20ELSE%20it%20doesn't%20appear%20to%20recognise%20it%20as%20zero.%3CBR%20%2F%3EThis%20doesn't%20preclude%20the%20possibility%20that%20I'm%20just%20doing%20it%20wrong...%3C%2FLINGO-BODY%3E
New Contributor

I'm running a FILTER function to create the input data for a Word mail merge document. What value do I test for in the mail merge for a blank cell returned by the FILTER function?

 

If I add two fields into the mail merge document and one of them is blank in the source data the mail merge field displays a zero. If I do a conditional test (IF THEN ELSE) in the mail merge on the source data using blank (" ") or zero as the test, neither find the blank value. I'm assuming that, behind the scenes, Excel is populating a "blank" cell result from a FILTER function as some special character or value (not blank or zero) and I just need to test for that. 

 

If I run the mail merge on the original (pre-FILTER) data it picks up the blank values correctly so I assume it's something about the way data is treated when processed by the FILTER function.

 

Anyone any ideas?

 

Thanks

 

4 Replies

@rhjtwo 

FILTER() returns blanks as zero.

Thanks Sergei,

It certainly appears to. When I print the value in mail merge it prints as zero. My issue is that if I test for the zero value in a conditional IF THEN ELSE it doesn't appear to recognise it as zero.
This doesn't preclude the possibility that I'm just doing it wrong...

@rhjtwo 

 

It sounds as if you're doing the IF THEN ELSE conditional test in Word. Which is fine; I've done the same on occasion. What I'm wondering is whether you could just (in Excel) change the values in those added cells--if I understood you again--to "Y" or "N" or something other than blank/zero. Since the value per se isn't apparently of significance in itself--not a part of an amount that needs to be calculated or some such--but is used to direct a custom sentence or paragraph--making it unmistakably "Y" or "N" might be an appropriate work-around.

Thanks mathetes,

I am indeed doing the conditional test in Word. Yours is a good idea unfortunately the fields are payee (from a list) in one field and the other field is other payee (free format, if the payee isn't on the list). There will only be one value so the test is IF there is a payee don't print the Other Payee field, so one field or the other must be blank. I've actually solved my immediate problem by using a more thoughtful boolean test but I thought I'd try to understand the underlying problem.

I think it'll be an issue between Excel and a fairly old bit of Word but I thought I'd start in this group to see if anyone has come across it.