If formula with length of field

[ejohnson]

I want to write a formula that says that if the length of a field is not 10, to then give back an invalid result.

I've tried to do this by creating a new field through the formula function and use the 'if' formula - but I don't know where to put the 'NOT' in...

IF Length([Tax number]) NOT "10" THEN "Invalid" ELSE " " ENDIF

This just leads to malformed if function error.

[BenMoss]

Hi @ejohnson 

For NOT EQUAL I would use !=

Also length returns a numeric value not string value, so 10 should not be in quotes.

IF Length([Tax number]) != 10 THEN "Invalid" ELSE " " ENDIF

Ben

[NJT · Accepted solution]

Try this out IF Length([TaxNumber]) != 10 THEN "Invalid" ELSE "" ENDIF if the result your looking for is invalid or blank.

[ejohnson]

Perfect! just needed that != syntax :-)

[JonquilP]

HI NJT,

For your example, what if I wanted to pull the detail from the first column to the new column if it meets my condition? What would insert for Then and Else?

For example, Column A Line 1 has 123456789. If I wanted to say If Length of Column A = 9, then place 123456879 in Column B Else "" ENDIF.

Thanks so much for your help.

[NJT]

I'm guessing you've figured this one out by now, sorry I didn't get a notification that there was another post here... You can just substitute the "" with your field name there.  

Try this out IF Length([ColumnName]) != 9 THEN "Invalid" ELSE [ColumnName] ENDIF if the result your looking for is invalid or the column name.