If formula with length of field
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
Solved! Go to Solution.
- Labels:
- Developer
- Expression
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Try this out IF Length([TaxNumber]) != 10 THEN "Invalid" ELSE "" ENDIF if the result your looking for is invalid or blank.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Perfect! just needed that != syntax :-)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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.
