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.

CodePudding user response：

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