nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Expression

Accepted answers

IraWatt

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%20Data

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com

Workflow.yxmd

All comments

IraWatt

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.

Any questions or issues please ask

Ira Watt
Technical Consultant
Watt@Bulien.com

Workflow.yxmd

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

USEIN.yxmd

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.

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15910

binu_acs 15783

MarqueeCrew 13710

apathetichell 13703