Find position of first number in string and split

I am attempting to find the first numeric character in an ID composed of a client segment identifier (ABC_), a geographic identifier (UK) and a number (1234567).

The ID will without exception follow this logic:

ABC_UK1234567

From this, I need to extract the number only (the location identifier will have different lengths, as it could be ABC_UK, ABC_CHR or ABC_I etc., as will the number itself).

I was attempting to use Right(ID, InStr(1,ID,“{1-9}“)) but am unsure how to specify that I am looking for ANY number (maybe a composite of OR statements in the InStr function).

Thank you for any help at all.

Answer

You can use this fancy expression in your query, where “1” will preserve a trailing zero:

StrReverse(Mid(CStr(Val("1" & StrReverse([ID]))),2)) As Code