hi All Please help in getting below "Want this" column. from thisWant thisAB-24-1234-1234-00000000CapturedAB-24-1234-1267-00000000Captured12-2345-0000012345-18Captured12-2345-0000067890-18Captured1234508Not CapturedwewerNot Captured Not Captured11-2345-0000067890-18Not CapturedOC-24-1234-1267-00000000Not Captured

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 numberGetAB-24-1234-1234-00000000CapturedAB-24-1234-1267-00000000Captured12-2345-0000012345-18Captured12-2345-0000067890-18Captured1234508NotwewerNot Not11-2345-0000067890-18NotOC-24-1234-1267-00000000Not

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

Help

Accepted answers

flying008

Hi, @shahnawaz_khan

FYI.

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

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

All comments

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

Hi, @shahnawaz_khan

FYI.

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

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703