Regex matching forumla pattern

[nxh038]

IF Contains([Value], "@") THEN "Email" ELSEIF REGEX_Match([Value], "^(?=.*?[0-9a-zA-Z])[0-9a-zA-Z]*[\/][0-9a-zA-Z,]*") THEN "ID" ELSE Null() ENDIF

The formula is working fine except for the regex I believe due to the pattern. As shown in the image, I need to match strings which have digits on either side of one forward slash. This shouldn't just match one instance, but should also match others in the same line if separated by a comma and space as shown. i.e. the pattern should be able to match all three lines shown in the example.

This should only match one slash as to not interfere with other fields such as dates which contain two slashes.

Thank you

[Christina_H]

Your Regex seems to work for a single ID, can you split the data to rows first?  See attached - this assigns IDs, splits to rows, uses your Regex formula to match IDs then joins everything back together.

[Ben_H · Accepted solution]

Hi @nxh038,

Could you try something like this instead?

IF Contains([Value], "@") THEN "Email"

ELSEIF REGEX_CountMatches([Value], "\d{6,}\/\d{3,}")>0 THEN "ID"

ELSE Null() ENDIF

I've just said count the values that match the pattern, if it's over 0 then use ID.

My Regex is a bit different too -

I've said we need a number of at least 6 digits, followed by a forward slash, then another number of at least 3 digits.

I didn't use actual lengths as I thought the IDs might vary a bit - either way it won't match to dates and you could modify to suit your case.

Regards,

Ben

[nxh038]

Perfect, thank you!!