regex to extract a number from strings using a template

Suppose to have multiple values that contains number following a pattern:

• 1a-p1_2023 -> extract 1 (the middle one)
• 1a-p12_2023 -> extract 12
• 1a-p7_2023 -> extract 7
• 1a-p145_2023 -> extract 145

the pattern is 1a-p#_2023 where # represent the number to be found (of any digits). Note that

1. The pattern can contains other digits (fixed and not wanted)
2. The pattern can contain letter and/or special characters
3. The pattern can contain the target digit(s) at the end, the start or in the middle
4. I want to use a special character (e. g. # )to identify uniquely the position of the target digits (not used in the other part of the template)

I think this could be obtain with regex with one of the following strategies:

1. extract the digit(s) extracting the part where # is
2. replace any other part of the string with the null character ""

The goal is to achieve that with REGEXEXTRACT or REGEXREPLACE in Google Sheets.

My tests:

"\d+" 

can’t be used because the first group of digit could not be the target.

"\#(.*?)\#" 

extracts the part between 2 "#" but i can’t extend the regex replacing a single # with half of the template. Also, a regex with the whole template string not splitted is possible?

>Solution :

Since the # character is used in your template to represent some one or more digits, you can use a 1a-p#_2023 template and replace # with (\d+) to create the full pattern:

=REGEXEXTRACT(A1;REPLACE("1a-p#_2023";"#";"(\d+)"))

See the regex demo.

Note the capturing group, it is necessary for the REGEXEXTRACT to return only that number captured into Group 1, not the whole match value.