help
- 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
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 |
Solved! Go to Solution.
- Labels:
- Help
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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:
For details, ask ChatGPT about RegEx, and it will explain.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Its working perfectly fine. Thanks
