Ask Search:
Dan RichDan Rich 

Nested IF AND returns a boolean-text error

The formula is supposed to check the stated conditions and return the appropriate field concats, however it bombs when it sees the IF)  AND)  statement. 

Any thoughts on correct syntax please?  Thanks you.


  IF( 

ISBLANK (TEXT_Parcel_Street_Suffix__c ), 

Parcel_Street_Number__c &" " &  SYS_Directional__c  & " " & Parcel_Street__c &" "&  Parcel_Unit__c,


  IF( 

ISBLANK (Parcel_Unit__c), 

Parcel_Street_Number__c &" "&SYS_Directional__c  &" "& Parcel_Street__c &" "& TEXT_Parcel_Street_Suffix__c,

  IF(
      
     AND(

ISBLANK (TEXT_Parcel_Street_Suffix__c ), ISBLANK (Parcel_Unit__c), 

Parcel_Street_Number__c &" " &  SYS_Directional__c  & " " & Parcel_Street__c,

Parcel_Street_Number__c &" "& SYS_Directional__c  &" "& Parcel_Street__c & " "& TEXT_Parcel_Street_Suffix__c &" "&Parcel_Unit__c))))
Best Answer chosen by Dan Rich
Steve MolisSteve Molis
Give this a try
IF( 
ISBLANK (TEXT_Parcel_Street_Suffix__c ), 
Parcel_Street_Number__c &" " &  SYS_Directional__c  & " " & Parcel_Street__c &" "&  Parcel_Unit__c,

IF( 
ISBLANK (Parcel_Unit__c), 
Parcel_Street_Number__c &" "&SYS_Directional__c  &" "& Parcel_Street__c &" "& TEXT_Parcel_Street_Suffix__c,

IF(
AND(
ISBLANK (TEXT_Parcel_Street_Suffix__c ), 
ISBLANK (Parcel_Unit__c))
, 
Parcel_Street_Number__c &" " &  SYS_Directional__c  & " " & Parcel_Street__c,
Parcel_Street_Number__c &" "& SYS_Directional__c  &" "& Parcel_Street__c & " "& TEXT_Parcel_Street_Suffix__c &" "&Parcel_Unit__c)))

 

All Answers

Steve MolisSteve Molis
Give this a try
IF( 
ISBLANK (TEXT_Parcel_Street_Suffix__c ), 
Parcel_Street_Number__c &" " &  SYS_Directional__c  & " " & Parcel_Street__c &" "&  Parcel_Unit__c,

IF( 
ISBLANK (Parcel_Unit__c), 
Parcel_Street_Number__c &" "&SYS_Directional__c  &" "& Parcel_Street__c &" "& TEXT_Parcel_Street_Suffix__c,

IF(
AND(
ISBLANK (TEXT_Parcel_Street_Suffix__c ), 
ISBLANK (Parcel_Unit__c))
, 
Parcel_Street_Number__c &" " &  SYS_Directional__c  & " " & Parcel_Street__c,
Parcel_Street_Number__c &" "& SYS_Directional__c  &" "& Parcel_Street__c & " "& TEXT_Parcel_Street_Suffix__c &" "&Parcel_Unit__c)))

 
This was selected as the best answer
Dan RichDan Rich
LOL--don't know what it different, but it worked perfectly.  Thanks much. As an aside, the field Parcel Street appears to be adding a blank character at the end of the concatonation if the field contains "street name street name" (Orange Apple) rather than the desired street name without whitespace at the end, as it is doing witjh "Street Name" (Orange). Have you ever before encountered this?
Steve MolisSteve Molis
I don't do a lot of Formula like this, lemme read through it and see if anything sticks out
Dan RichDan Rich
Thanks--I may have fixed it with a couple of TRIMS.  Beer count approaching a twelve pack...