Quick problem - string mix of letters & numbers - increment by one

Just posting a quick problem I have. It shouldn't be too hard but I'm under the weather and can't work how to tackle it right now.

Say you have a string which is a reference number. It has alphabetical characters at the front of the string and numerals for the rest. e.g. "XYZ42567" I need a function that I can pass this string to and it will generate the next string. so from the example, the function would return "XYZ42568".

Also we can't guess what the letters are for, so if the input argument is "XYZ999" then an input box should be shown to the user that the function can't predict the next code so it will need it manually entered this time.


To make it easier, I currently only need it for strings that are in the format of letters followed by numerals.

(But ideally the function should accept a complete mix of numerals & letters. The function only increments the last block of numerals from the right).

is it always 3 letters?

=LEFT(A1,3)&MID(A1,4,10)+1

if it is
assuming a1 is cell in reference

No. I only used 3 letters in the example.

So as you can see there are few little string tricks that will be needed here. e.g. Detect rightmost non-numeric & RIGHT & LEN etc.

How about just dragging down the fill handle?

array formula
shift+ctrl+enter

=LEFT(A1,(MIN(FIND({0;1;2;3;4;5;6;7;8;9},A1&{0;1;2;3;4;5;6;7;8;9}))-1))&1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$9),1)),0),COUNT(1*MID(A1,ROW($1:$9),1)))+1

does not work if starts with numeric

extracting number from string found here
http://office.microsoft.com/en-au/ex...001154901.aspx

the $9 represents the number of characters so if over 9 increase

perhaps

Function GetNextID(sIn As String) As String
   Dim matches
   Dim lTemp As Long
   Dim lMatch As Long
   Dim lLen As Long
   With CreateObject("vbscript.regexp")
      .Pattern = "([\d]+)"
      .Global = True
      If .Test(sIn) Then
         Set matches = .Execute(sIn)
         With matches(matches.Count - 1)
            lTemp = .Value
            lMatch = .FirstIndex#
            lLen = .Length
         End With
         If Len(CStr(lTemp + 1)) > Len(CStr(lTemp)) Then
            GetNextID = "Cannot compute-please enter manually"
         Else
            If lMatch = 0 Then
               GetNextID = CStr(lTemp + 1)
            Else
               ' numbers must be padded for any leading zeroes
               GetNextID = Left$(sIn, lMatch) & Format(lTemp + 1, String(lLen, "0"))
            End If
         End If
      Else
         ' no numbers so just return input string
         GetNextID = sIn
      End If
   End With
End Function

@ shg & humdingaling = thanks for the suggestions but that is not what I am looking for because:
1. I don't need this function for an excel range and
2. I don't know how the end user deals with a part reference like "ABCXYZ999" + 1 (Is it "ABCXZA000"? is it "ABCXZA001"? is it "ABCXYZ1000"? I don't know. Not sure the end user is either! ) So the end user needs to be prompted if this happens.

@ JosephP = once again you provide the answer. +1


I'll mark this as solved but I'm uneasy about using vbscript through VBA. How compatible is this?

UPDATE: Below was solved by shg.
I'll rep anyone who can provide code that works similar to Josephs but using standard VBA methods only (e.g. LEN, RIGHT, MATCH etc).

it should work on windows versions of excel

Originally Posted by JosephP

it should work on windows versions of excel

It does.

I was thinking of Mac users.

I guess someone oughta ;-)

Function GetNextID(sInp As String) As String
    Dim i As Long
    Dim s As String
    
    For i = 1 To Len(sInp)
        s = Mid(sInp, i)
        If s Like String(Len(s), "#") Then Exit For
    Next i
    
    If i > Len(sInp) Then
        GetNextID = sInp
    Else
        GetNextID = Left(sInp, Len(sInp) - Len(s)) & s + 1
        If Len(GetNextID) > Len(sInp) Then GetNextID = "???"
    End If
End Function

Thanks shg. +1

I don't know if it's an issue for what you're doing but shg's current version does not retain leading zeroes in the numbers

Thanks for pointing that out. I haven't come across any part or reference numbers with leading zeros before any numbers but I would like the function to handle it if it ever does.

The output string should be the same character length as the input string with the last set of numerics in the string incremented by one. Any leading zeroes in the last set of numerics in the string should be kept (to maintain string length). Where the input strings last set of numerics are all 9 then the function should return an input box to ask the user for a manual entry - see post #7 for the reasons on this.

I am thinking the detection part of could be solved by changing this line

If s Like String(Len(s), "#") then

to

If s Like String(Len(s), "#") and not left(s,1) = 0 then

(untested)

UPDATE: Below solved by shg
I'll rep anyone who can edit the code to achieve the outcome in the 2nd paragraph of this post. Thanks

GetNextID = Left(sInp, Len(sInp) - Len(s)) & Format(s + 1, String(Len(s), "0"))

That was quick!

I'll test it out and get back to you. (What happened to the input box? )

Originally Posted by shg

GetNextID = Left(sInp, Len(sInp) - Len(s)) & Format(s + 1, String(Len(s), "0"))

Close. I tested it and it only works with one leading zero. ("ABCXYZ0999" = works, "ABCXYZ00999" = fail)

Below is my current version of the code:

Public Function NextReferenceNumber(ByVal strInput As String) As String
    Dim bytCharacter As Byte
    Dim strCharacter As String

    For bytCharacter = 1 To Len(strInput)
        strCharacter = Mid(strInput, bytCharacter)
        If strCharacter Like String(Len(strCharacter), "#") Then Exit For
    Next bytCharacter

    If bytCharacter > Len(strInput) Then
        NextReferenceNumber = strInput
    Else
        NextReferenceNumber = Left(strInput, Len(strInput) - Len(strCharacter)) & strCharacter + 1 'previous version
        'NextReferenceNumber = Left(strInput, Len(strInput) - Len(strCharacter)) & Format(strCharacter + 1, String(Len(s), "0")) 'only allows one leading zero
        If Len(NextReferenceNumber) > Len(strInput) Then NextReferenceNumber = "???" 'to add input box here
    End If
End Function

I tested it and it only works with one leading zero. ("ABCXYZ0999" = works, "ABCXYZ00999" = fail)

      -----A----- -----B-----
  1   A01         A02        
  2   A001        A002       
  3   A1001       A1002      
  4   A00001      A00002     
  5   ABC00002    ABC00003   
  6   ABCXYZ0998  ABCXYZ0999 
  7   ABCXYZ0999  ABCXYZ1000 
  8   ABCXYZ00999 ABCXYZ01000
  9   ABCXYZ01000 ABCXYZ01001

Those all look right to me.

I was testing the code in post #17 (uncomment the 3rd last line & comment the 4th last line) with these commands in the immediate window:

? NextReferenceNumber("ABCXYZ0999")

and

? NextReferenceNumber("ABCXYZ00999")

- they both gave me the result of ABCXYZ1000

Dunno what to tell you.

Function GetNextID(sInp As String) As String
    Dim i           As Long
    Dim s           As String

    For i = 1 To Len(sInp)
        s = Mid(sInp, i)
        If s Like String(Len(s), "#") Then Exit For
    Next i

    If i > Len(sInp) Then
        GetNextID = sInp
    Else
        GetNextID = Left(sInp, Len(sInp) - Len(s)) & _
                    Format(s + 1, String(Len(s), "0"))
        If Len(GetNextID) > Len(sInp) Then GetNextID = "???"
    End If
End Function

      -----A----- -----B-----
  1   ABCXYZ0999  ABCXYZ1000 
  2   ABCXYZ00999 ABCXYZ01000

Sorry shg. Your code was right all along. My apologies for wasting your time.
+1 for successful solving as stated in post #14.


(I was using my version of your code - see post #17 - and it wouldn't handle more than one zero. When I tried your version - post #20 - it worked. Then I realised. I had missed changing one variable. Lesson learnt = Never, never, turn off Option Explicit. I don't need to and it's not worth it! )

Thanks jindon. However I am avoiding using RegEx (see post #7).

My assumption, sorry. BTW I repped you for contributing this code in case it helps another forum user who wishes to use a RegEx version.

This is a little more robust:

Function GetNextID(sInp As String) As String
    Dim i           As Long

    For i = 1 To Len(sInp)
        If Mid(sInp, i) Like String(Len(sInp) - i + 1, "#") Then Exit For
    Next i

    If i > Len(sInp) Then
        GetNextID = sInp
    Else
        GetNextID = Left(sInp, i - 1) & _
                    Format(Mid(sInp, i) + 1, String(Len(sInp) - i + 1, "0"))
        If Len(GetNextID) > Len(sInp) Then GetNextID = "???"
    End If
End Function