What RegEx can I use here?

[mshin]

Hello there all!  I have a data set per below.  As you can see there are a number of : within the string.  I only want to pull all numbers to the RIGHT of the last colon (:).

When I use below function, it is not always pulling the numbers after the last colon.  I eventually want this attribute to be an integer for downstream stuff.

right([Respondent ID], FindString([Respondent ID], ':'))

Any thoughts on how I can extract this?

Mike

[ScottLewis · Accepted solution]

The Regex 

(.*:)(.*)

In a Regex tool set to Parse should give you what you need. That will output everything up to and including the last ":" in one column and everything after that in a second column. 

[Luke_C · Accepted solution]

Hi @mshin 

This regex would work, assuming it will always be numeric:

REGEX_Replace([Respondent ID], ".*:(\d+)$", "$1")

Explanation of each part (courtesy of chatgpt):

Explanation of Each Part

1. .*

   • . → Matches any character (except a newline).
   • * → Matches zero or more of the preceding character (.), meaning it will consume everything up to the last colon (:).
   • This ensures we capture everything before the final numeric sequence.

2. :

   • Matches the literal colon character (:).
   • This ensures we find the last occurrence of : in the string.

3. (\d+)$

   • ( and ) → Captures whatever is inside (used for extraction).
   • \d+ → Matches one or more (+) digits (\d means any digit from 0-9).
   • $ → Anchors the match to the end of the string, ensuring we extract only the numbers after the last colon.

Replacement ("$1")

• "$1" refers to the first capture group, which is (\d+).
• This means it replaces the entire string with just the numbers found after the last :.

[ScottLewis · Accepted solution]

If you don't want to RegEx, you can accomplish the same thing by nesting ReverseString with FindString to get the index of the last ":" and then cut from there using Right.

[mshin]

@Luke_C .  This solution worked!  I also appreciate the details.  I'll have to digest this a bit as this syntax is new to me. :)