Length formula-

[Suryachoppakatla]

Hello,

I will have a data of US EIN number something like this (015-46-1210) which is a combination of Numeric digits & Hyphens.

For this kind of data, I want to write a formula using "IF Length" or any other; and the formula should satisfy the below conditions 

1) The EIN must have Nine numeric digits. If they are eight or Ten, then the result should show "Invalid" otherwise "Valid" (only if Nine digits available)

2) The Hyphens are acceptable. However there must be Nine digits all the time

The below condition satisfies because, the condition is considering both Numeric digits + Characters (hyphens) in this case. However, there is every possibility a human error might happen wherein there might be more or less numeric digits or typo error, could be anything.

In such cases I should be able to catch the error 

Please suggest any idea.

Thanks

Surya

[IraWatt · Accepted solution]

Hey @Suryachoppakatla,

One way of doing this is to use a regex function to get all the numbers from your text then measure the length just the numbers:

REGEX_Replace([Field1], "\D", "")

The function above replaces any non-number character with empty space allowing you to count the length of just the numbers.

If you want to learn more about Regex the community has some really quick interactive videos on getting to grips with it here https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Parsing%20...

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com 

[DataNath]

@Suryachoppakatla similar to @IraWatt’s suggestion, using RegEx, you could also use the REGEX_CountMatches() function to count the occurrences of numbers (\d). If it’s 9 then assign the valid flag and invalid if not. More info here:

https://help.alteryx.com/20221/designer/string-functions

[Suryachoppakatla]

Hi Ira Watt,

Thank you so much for your quick response it worked

Best Regards,

Surya

[IraWatt]

No worries @Suryachoppakatla, also a much cleaner solution suggested by @DataNath 😅

[DenisZ]

Here is another way of doing it

Hope this helps

[Suryachoppakatla]

My Sincere thanks to all 3 of you, I was looking for one solution and I got 3 😊

I just got an idea, when I run this formula in my app, the output has come exactly as I desired. However, I am just wondering is there a possibility that all "Valid" should be in Green color (only text) and all "Invalid" are in RED color. This will help to easily catch the "Invalids" and can fix them.

I do not want to use Table tool here for coloring. 

Looking for something very simple to embed in above formula, just incase if possible 

Regards...

[DataNath]

Hello @Suryachoppakatla - to apply that kind of formatting, you would need to use a Table tool unfortunately and set up a column rule.