I think that what you are trying to do is to get the next 'number' in the sequence that you specify.
I have written a user defined function to do this as I couldn't come up with a formula. If someone else comes up with a formula you should use it.
Anyway this user defined function is used as follows:
In Cell A2 is your stem, e.g., A0101
In cell B2 is the user defined function NxtSeq, as follows:
NxtSeq takes two arguments. The first is a range of cells which contain existing alphanumeric values, and the second is the cell that contains the stem - cell A2 in this case.
The range for the existing alphanumeric values should be larger than the used range, so that when new values are added to the list, this formula will automatically update to show the next alphanumeric.
To create the user defined function do this:
From your worksheet, click Alt+f11 (the Alt key and function key #11 together).
This opens the Visual Basic window.
The Project explorer pane should be visible on the left - if not, from the VB menu select View - Project Explorer.
Find your workbook in the Project explorer - like this: 'VBA Project(MyWorkbook.xls)'.
Right-click it and select Insert and Module (not Class module)
Double click the new module name (Module1).
In the VB window on the right enter this:
Public Function NxtSeq(Source As Range, Base As String) As Variant
Dim dblNum As Double
Dim rngCell As Range
Dim blnFirstMatch As Boolean
Dim rngLrgst As Range
Dim n As Integer
On Error GoTo ErrHnd
'set flag to base not matched
blnFirstMatch = False
'loop through all cells in the range
For Each rngCell In Source.Cells()
'test if value has the required stem
If Left(rngCell.Text, Len(Base)) = Base Then
'if this is the first match for this base, set the Largest Range
If blnFirstMatch = False Then
Set rngLrgst = rngCell
'set flag to show we have an initial range
blnFirstMatch = True
'get the number part of the text, after the base
dblNum = CDbl(Right(rngCell.Text, Len(rngCell.Text) - Len(Base)))
'test if it's bigger than the first one we have
If dblNum > CDbl(Right(rngLrgst.Text, _
Len(rngLrgst.Text) - Len(Base))) Then
'reset the largest range to this cell
Set rngLrgst = rngCell
'make sure we found a match for the base - if not exit
If blnFirstMatch = False Then NxtSeq = CVErr(xlErrName): Exit Function
'match found so get the largest number found
dblNum = CDbl(Right(rngLrgst.Text, Len(rngLrgst.Text) - Len(Base)))
'increment by 1
dblNum = dblNum + 1
'convert back to text and add it to the base
NxtSeq = Base & Format(dblNum, Application.WorksheetFunction. _
Rept("0", Len(rngLrgst.Text) - Len(Base)))
'return the NA error
NxtSeq.Value = CVErr(xlErrNA)
Click Save from the VB menu.
Use Alt+f11 to return to the main Excel window.
Enter the user-defined function with its two parameters.
If the base is not found the function returns the #NAME error.
Hope this works.