Increment Alpha/Numeric String

[Jason Gyetko]

I have an alpha/numeric string which gets entered into a textbox. The
string ends in numeric values. I need to read (from right to left) all
characters until I hit an alpha character then increment the last numeric
portion. Anyone know how I can do this? Do I use InStr(), if so, not sure
how to specify my starting point? Thanks.

Ex:
abc123def45678 increment to abc123def45679

 

[Brendan Reynolds]

Public Function IncTrailDigit(ByVal strInput As String) As String

Dim lngLoop As Long
Dim strChar As String
Dim strWork As String

For lngLoop = Len(strInput) To 1 Step -1
strChar = Mid$(strInput, lngLoop, 1)
If IsNumeric(strChar) Then
strWork = strChar & strWork
Else
Exit For
End If
Next lngLoop
IncTrailDigit = Left$(strInput, Len(strInput) - Len(strWork)) +
CStr(Val(strWork) + 1)

End Function

But what's supposed to happen when you reach abc123def99999?

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.

 

[Dale Fye]

Jason,

Looks to me like you are storing more than one piece of information in this
field. Why not split the field so that its various component parts are each
in their own column? then you wouldn't have to go through this each time,
you would just increment the number and display it with leading spaces using
the format() function.

 

[Jason Gyetko]

Thanks, works perfect.

It may be 6 or 7 trailing digits, I'm not sure on the specs yet... but at
any rate, I don't think we'll get to that point.

 

[Jason Gyetko]

I agree, that would be a better solution, but it's a custom request. In
most cases this number will just be a 6 to 8 digit numeric value. I don't
want to conform our standards or database for a specific instance,
especially with the amount of overhead that would be envolved.