How to I remove the too many arguments error for the If AND function?-CodePudding

We have job numbers most with a leading 1, with 7 digits to follow. There is one set, that leads with a 6, and is 6 digits. I need a leading '0' on both

The below formula captures the '0', for job numbers leading with a 1, with 7 digits, but not the items leading with a 6, with 6 digits:

Z Sage ID = IF(LEFT('Sales 
Force_Contract'[Account_Number__c],1)="1",CONCATENATE("0",'Sales 
Force_Contract'[Account_Number__c]),'Sales Force_Contract'[Account_Number__c])

Here's what I tried, and received this error:

Z Sage ID = IF(LEFT('Sales 
Force_Contract'[Account_Number__c],1)="1",IF(and('Sales 
Force_Contract'[Account_Number__c]="6",CONCATENATE("0",'Sales 
Force_Contract'[Account_Number__c]),'Sales Force_Contract'[Account_Number__c])))

'Too many arguments were passed to the AND function. The maximum argument count for the function is 2.'

Can you help get on the right track?

CodePudding user response：

The easiest way is to use operators that don't require you to add words like AND() Read more about it here.

The following code should work:

Z Sage ID = 
IF (
    LEFT('Sales Force_Contract'[Account_Number__c],1) = "1" ||
    LEFT('Sales Force_Contract'[Account_Number__c],1) = "6",
    "0" & 'Sales Force_Contract'[Account_Number__c],
    'Sales Force_Contract'[Account_Number__c]
   )

How it works:

It checks if the left number is either a 1 or (Or = ||) a 6.
If yes, it adds a leading 0 (One & (&) to concatenate, two & (&&) to get the AND() function)
If no, it takes the original account number.