Suppose I have strings like:
ABC-L-W7P-1423
ABC-L-W7E-87
CH-L-W7-756
I need to grab the number at the end. That number might be 2, 3 or 4 digits. But currently what I have is:
=REGEXREPLACE(B2,"[^0-9]","")
Which of course also grabs the '7' in 'W7P' which I don't want. EDIT:
I also need to match something like this:
CH-M-311-MM
So always a 2, 3 or 4 (or 5) digit number, but I need single digits excluded.
You can use =REGEXEXTRACT
with \b[0-9]{2,4}\b
:
=REGEXEXTRACT(B2, "\b[0-9]{2,4}\b")
See the regex demo.
Details:
\b
- a leading word boundary[0-9]{2,4}
- 2 to 4 digits\b
- trailing word boundaryIn case your 2-4 digits are always preceded with -
, you may use
=REGEXREPLACE(B2,"^.*-([0-9]{2,4})\b.*","$1")
See this regex demo
Details:
^
- start of string.*-
- any 0+ chars up to the last -
that is followed with...([0-9]{2,4})
- (Group 1 referred to with $1
in the replacement pattern) - 2 to 4 digits\b
- a trailing word boundary.*
- any chars up to the end of string.