help

[shahnawaz_khan]

hi All

Please help in getting below "Want this" column.

from this	Want this
AB-24-1234-1234-00000000	Captured
AB-24-1234-1267-00000000	Captured
12-2345-0000012345-18	Captured
12-2345-0000067890-18	Captured
1234508	Not Captured
wewer	Not Captured
	Not Captured
11-2345-0000067890-18	Not Captured
OC-24-1234-1267-00000000	Not Captured

[flying008]

Hi, @shahnawaz_khan 

What is the rule of get ?

[shahnawaz_khan]

if numbers are in below format and in below length (24 & 21) then it will called as "captured" other wise it will consider as "not captured".

from this	Want this	Length
AB-24-1234-1234-00000000	Captured	24
AB-24-1234-1267-00000000	Captured	24
12-2345-0000012345-18	Captured	21
12-2345-0000067890-18	Captured	21

Not Capture as the details are not in the format or the length of the number is not acurate.

1234508	Not Captured	7
wewer	Not Captured	5
	Not Captured	0
11-2345-0000067890-18	Not Captured	21
OC-24-1234-1267-00000000	Not Captured	24

[ntakeda]

I don't fully understand the rules of "Captured", but this can be achieved using the RegEx tool.

• \d represents a digit.
• \w represents a letter or a digit.
• [A-Z] represents an uppercase letter.
• {n} specifies the number of characters.
• If there are multiple patterns, use | to separate them.

For example, if the your pattern consists of:

• Uppercase letters (2) - Digits (2) - Digits (4) - Digits (4) - Digits (8)
• OR Digits (2) - Digits (4) - Digits (10) - Digits (2)

The regex would be:

[A-Z]{2}-\d{2}-\d{4}-\d{4}-\d{8}|\d{2}-\d{4}-\d{10}-\d{2}

For details, ask ChatGPT about RegEx, and it will explain.

[shahnawaz_khan]

Hi,

A policy number that starts with "AB-" should have a length of 24 characters, and a policy number that starts with "12-" should have a length of 21 characters.

If a policy number starts with "AB-" or "12-" and has the corresponding length of 24 or 21 characters respectively, then it is considered valid (True). Otherwise, it is considered invalid (False).

I have also mention the reason for better under standing

Policy number	Length	output	Reason
AB-24-1234-1234-00000000	24	TRUE	Matched with criteria hence true
AB-24-1234-1267-00000000	24	TRUE	Matched with criteria hence true
12-2345-0000012345-18	21	TRUE	Matched with criteria hence true
12-2345-0000067890-18	21	TRUE	Matched with criteria hence true
1234508	7	FALSE	Not matching with the criteria hence false
wewer	5	FALSE	Not matching with the criteria hence false
	0	FALSE	Not matching with the criteria hence false
11-2345-0000067890-18	21	FALSE	Not matching with the criteria hence false
OC-24-1234-1267-00000000	24	FALSE	Not matching with the criteria hence false

[flying008 · Accepted solution]

Hi, @shahnawaz_khan 

FYI.

IIF((StartsWith([Policy number], 'AB') && Length([Policy number]) = 24) or (StartsWith([Policy number], '12') && Length([Policy number]) = 21), 'Captured', 'Not')

or

REGEX_Match([Policy number], '^AB-\d{2}-\d{4}-\d{4}-\d{8}$') or REGEX_Match([Policy number], '^12-\d{4}-\d{10}-\d{2}$')

Policy number	Get
AB-24-1234-1234-00000000	Captured
AB-24-1234-1267-00000000	Captured
12-2345-0000012345-18	Captured
12-2345-0000067890-18	Captured
1234508	Not
wewer	Not
	Not
11-2345-0000067890-18	Not
OC-24-1234-1267-00000000	Not

[shahnawaz_khan]

Its working perfectly fine. Thanks