What RegEx can I use here?

Question

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 Answer

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 Answer

Hi @mshinn9999

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

.*

. → 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.

:

Matches the literal colon character (:).

This ensures we find the last occurrence of : in the string.

(\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 Answer

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.