I need to find the latest internal code in a long google sheet, so i know the next number to use in a equipment database… Im new to Regex.

My data looks like this:

chggpr-001
chggpr-001
chggpr-001
chggpr-001
chggpr-001
chggpr-001
chggpr-001
chgaa-001
chgaa-001
chgaa-001
chgaa-001
chgaa-001
chgaa-001
chgaa-001
bataa-001
chgusb-001
camacc-015
camacc-016
lensfe-002
vidmon-002
lensfe-003
lensfe-004
cam-003

let’s say i input the string "camacc" into field B1, i want B2 to return 017, because thats the next number of that code available. This way i know i need to add the new items to that number..

Right now i’m walking blindly, but this regex seems to work in the editor: camacc\-(\d+)

In google sheets: =arrayformula(MAX(0+iferror(regexextract(B2:B580, "chggpr\-(\d+)"))))
that does not work…

Any tips on how to regex out the string based on cell input, then get the max value?

>Solution :

You can try:

=TEXT(MAX(INDEX(--IFNA(REGEXEXTRACT(A:A,B1&"-(\d+)"))+1)),"000")