cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
OzzieFrog Regular Visitor
Regular Visitor

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 some value I want to include/exclude in my report and the only way to identify it is to assess whether it complies with the following format: BUN-D-016902-000. Effectively 3 digits - 1 letter - 6 digits - 3 digits.

 

I have used search("-",.......) but it still includes data I wish to exclude. ANy thoughts?

 

Thanks in advance Smiley Happy

 

OF

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
OzzieFrog Regular Visitor
Regular Visitor

Re: Column to identify if text string in correct format

Ok it's not pretty but I think I got it Smiley Happy

 

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")
2 REPLIES 2
Highlighted
OzzieFrog Regular Visitor
Regular Visitor

Re: Column to identify if text string in correct format

Ok it's not pretty but I think I got it Smiley Happy

 

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")
CR Member
Member

Re: Column to identify if text string in correct format

Hi @OzzieFrog

 

You can add a custom column through your query editor with the following code:

 

if Text.Length([Name]) <>  16 then "NO 0"
 else if Text.Middle([Name],5,1) <> "-" then "NO 1"
 else if Text.Middle([Name],7,1) <> "-" then "NO 2"
 else if Text.Middle([Name],14,1) <> "-" then "NO 3"
 else "YES"

1.png

It will show you what is wrong and you will select the YES or the ones starting with NO for your count.

 

NOTE => I didn't test it so to be checked first in your PBIX !

 

Regards,

CR