Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Column to identify if text string in correct format

Hi all,   I have tried several formulas to achieve what I am looking for and have only had mixed results so I was hoping someone would have a clever idea.   I have a column (Name) which contains ...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Ok it's not pretty but I think I got it :)

     

    EDM/SPF Format Checker = 
    iferror(IF(ISTEXT(mid('All Documents'[Name],1,1))=TRUE&&
    ISTEXT(mid('All Documents'[Name],2,1))=TRUE&&
    ISTEXT(mid('All Documents'[Name],3,1))=TRUE&&
    SEARCH("-",'All Documents'[Name],1,0)=4&&
    ISTEXT(mid('All Documents'[Name],5,1))=TRUE&&
    SEARCH("-",'All Documents'[Name],5,0)=6&&
    ISNUMBER(VALUE(mid('All Documents'[Name],7,1)))=TRUE&&
    ISNUMBER(VALUE(mid('All Documents'[Name],8,1)))=TRUE&&
    ISNUMBER(VALUE(mid('All Documents'[Name],9,1)))=TRUE&&
    ISNUMBER(VALUE(mid('All Documents'[Name],10,1)))=TRUE&&
    ISNUMBER(VALUE(mid('All Documents'[Name],11,1)))=TRUE&&
    ISNUMBER(VALUE(mid('All Documents'[Name],12,1)))=TRUE,
    "EDM/SPF Format","Incorrect Format"),"Incorrect Format")
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Ok it's not pretty but I think I got it :)

 

EDM/SPF Format Checker = 
iferror(IF(ISTEXT(mid('All Documents'[Name],1,1))=TRUE&&
ISTEXT(mid('All Documents'[Name],2,1))=TRUE&&
ISTEXT(mid('All Documents'[Name],3,1))=TRUE&&
SEARCH("-",'All Documents'[Name],1,0)=4&&
ISTEXT(mid('All Documents'[Name],5,1))=TRUE&&
SEARCH("-",'All Documents'[Name],5,0)=6&&
ISNUMBER(VALUE(mid('All Documents'[Name],7,1)))=TRUE&&
ISNUMBER(VALUE(mid('All Documents'[Name],8,1)))=TRUE&&
ISNUMBER(VALUE(mid('All Documents'[Name],9,1)))=TRUE&&
ISNUMBER(VALUE(mid('All Documents'[Name],10,1)))=TRUE&&
ISNUMBER(VALUE(mid('All Documents'[Name],11,1)))=TRUE&&
ISNUMBER(VALUE(mid('All Documents'[Name],12,1)))=TRUE,
"EDM/SPF Format","Incorrect Format"),"Incorrect Format")