Forum Discussion

MitchTrott's avatar
MitchTrott
Frequent Visitor
2 years ago
Solved

Column format check

Hi all,    I need a custom column to check the values of another column are of the correct format. the format being ABCD1234. If the format is anything different i'd like it to be flagged. I cannot...
Need Help
Tips and Tricks
  • MitchTrott's avatar
    MitchTrott
    2 years ago

    Hi, I have fixed the error with an added IFERROR clause.

     

    Check 4x4 Format =

    VAR text1 = [4x4]

    VAR check1 = LEN(text1) = 8

    VAR check2 = EXACT(LEFT(text1, 4), UPPER(LEFT(text1, 4)))

    VAR check3 = ISNUMBER(IFERROR(VALUE(RIGHT(text1, 4)), BLANK()))

    RETURN IF(check1 && check2 && check3, "Correct Format", "Incorrect Format")

     
    Thank you for your help!!
MitchTrott's avatar
MitchTrott
Frequent Visitor
2 years ago

That looks great, thank you although im getting an error: Cannot convert value '' of type Text to type Number.

 

Also, in another table, cannot convert value '852A' of type text to type number

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi MitchTrott ,

My column is the text type.Can you share a screenshot of your error, please?

Best Regards,

Xianda Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • MitchTrott's avatar
    MitchTrott
    Frequent Visitor
    2 years ago

  • MitchTrott's avatar
    MitchTrott
    Frequent Visitor
    2 years ago

    Hi, I have fixed the error with an added IFERROR clause.

     

    Check 4x4 Format =

    VAR text1 = [4x4]

    VAR check1 = LEN(text1) = 8

    VAR check2 = EXACT(LEFT(text1, 4), UPPER(LEFT(text1, 4)))

    VAR check3 = ISNUMBER(IFERROR(VALUE(RIGHT(text1, 4)), BLANK()))

    RETURN IF(check1 && check2 && check3, "Correct Format", "Incorrect Format")

     
    Thank you for your help!!