Is there a function or VBA that can read multiple strings within a single cell?

I have a table that has production numbers for a machine I run. They all start with the letter O and there are 4 different production types, e.g., O0000, O1000, O2000, and O3000. I have a table to the side that tells me what the next production number is for each type.

Sometimes, I need a couple production numbers for one project. I've used simple MID and RIGHT functions to split the production number into two columns and a MAXIFS function to determine the largest production number per type to determine what my next production number is.

The only problem is that I am planning to have 2+ production numbers in future projects. So, is there a function (or VBA code) that can read each 5 character string beginning with O in a cell and accurately determine the largest value without needing to create new columns ? I prefer to see all the production numbers for the project in one consistent column for readability.

The last row is an example of a project with 3 production numbers. Ideally, the next Lathe production number should read O0124.

If you have any questions, let me know and I'll try my best to answer.

Couldn't find a way to make it work with worksheet formulas, maybe someone else can revise my other answer. Here is a solution with a custom function.

The function is named LARGEPROG and takes 3 arguments: the range containing the text you want to search, the minimum value you want to find, and the maximum value you want to find.

If you want the O2000's you would use =LARGEPROG(A1:A5,2000,3000)

Function LARGEPROG(prog_text As Range, minimum_value As Long, maximum_value As Long) As Long

Dim rg As Range
Dim rgString As String
Dim MaxValue As Long
Dim vValue As Variant

For Each rg In prog_text
    
    'Remove "O"
    rgString = Replace(rg, "O", "")
    
    'Choose delimiter
    If InStr(rgString, "-") Then
        delim = "-"
    ElseIf InStr(rgString, ",") Then
        delim = ","
    Else
        delim = ""
    End If
    
    'Split range text
    arr = Split(rgString, delim)
    
    'Check if each value is within range
    For Each vValue In arr
        If vValue >= minimum_value And vValue < maximum_value And vValue > MaxValue Then MaxValue = vValue
    Next vValue

Next rg

LARGEPROG = MaxValue

End Function

SUBSTITUTE will get rid of the letter "O".

TEXTSPLIT will split the string by the "-" or ",".

NUMBERVALUE will turn the text into a number.

MAX will pick the highest number.

Then you need 2 different formulas, depending on whether your text is separated by "-" or ",". Or use LET to determine which delimiter to use:

=IF(ISNUMBER(FIND("-",A1)),
MAX(NUMBERVALUE(TEXTSPLIT(SUBSTITUTE(A1,"O",""),"-"))),
MAX(NUMBERVALUE(TEXTSPLIT(SUBSTITUTE(A1,"O",""),","))))

Or

=LET(DELIM,IF(ISNUMBER(FIND("-",A1)),"-",","),
MAX(NUMBERVALUE(TEXTSPLIT(SUBSTITUTE(A1,"O",""),DELIM))))

Edit:
More complicated if you need the maximum value from the range in a single cell but this should do it:

=MAX(BYROW(A1:A2,LAMBDA(PROGRAMTEXT,LET(DELIM,IF(ISNUMBER(FIND("-",PROGRAMTEXT)),"-",","),
MAX(NUMBERVALUE(TEXTSPLIT(SUBSTITUTE(PROGRAMTEXT,"O",""),DELIM)))))))