Need help parsing a string - reverse Val?

G

google

I'm trying to implement something that would speed up data entry. I'd
like to be able to take a string, and increment ONLY the right-most
numerical characters by one. The type structure of the data that is in
this field can vary. It's a list of mechanical equipment, and how it
is designated varies based on how the customer has them labeled. For
example, a list of their equipment might look like:

CH-1
CH-2
CH-3

or it might be

AH-1-1
AH-1-2
AH-1-3

or even

FC-1-1G
FC-1-2G
FC-1-3G

And there will be times where it's even more obscure, but I'd say at
least 75% of the time, the right-most numerical part of the string will
increment like above. It seems that what I need is to somehow get the
Val function to read from right to left (it does stop at dashes,
right?), pull out that number, increment it, then shove it back in the
string. I already have my form set up so that the field automatically
populates with the value from the previous record (so they only have to
go in & edit the number to be correct instead of typing the entire
string), but what I'd like to do is also implent code on a double-click
that will take the value of that field, and do the above incrementing
(which would have the added advantage of making it easy when units are
skipped... just double-click twice).

Any ideas?

Thanks!
 
T

Terry Kreft

This seems towork

Function IncrLast(RHS As String) as String
Dim intInstr As Integer
Dim strLeft As String
Dim strRight As String
Dim strAlpha As String
Dim intNumber As Integer

intInstr = InStrRev(RHS, "-", Compare:=vbBinaryCompare)

strLeft = left(RHS, intInstr)
strRight = Mid(RHS, intInstr + 1)
intNumber = Val(strRight)
strAlpha = Mid(strRight, Len(intNumber))
intNumber = intNumber + 1

IncrLast = strLeft & intNumber & strAlpha
End Function

Test values
?IncrLast("CH-1")
CH-2
?IncrLast("AH-1-1")
AH-1-2
?IncrLast("FC-1-1G")
FC-1-2G
 
G

google

Excellent! First pass seems to work, EXCEPT once you get to 10. It
wants to make the next number 110. In fact, it starts to fail on
anything more than single digit numbers. I'm not very good at all
these functions, I need to look them all up to see what they do to see
if I can understand what's happening.
 
G

google

Ok, now that I see what InStrRev does, that may be part of the problem
(and will be for other formats that don't use a "-", like AHU1. I
assume that the defined position of the dash is what's causing the
numbers to grow once the numeric portion that is being incremented gets
past a single digit. I'll have to digest some more.
 
G

google

Hmmm, I'm still having trouble. I took yours, then re-worked it with a
different approach (some unneeded declarations are still in there):


Private Function IncrLast(RHS As String) As String
Dim intInstr As Integer
Dim strLeft As String
Dim strRight As String
Dim strAlpha As String
Dim intNumber As Integer
Dim revString As String
Dim revNumber As Integer
Dim revNumStr As String
Dim revNewNumber As String
Dim NumLength As Integer
Dim NumPos As Integer

revString = StrReverse(RHS) ' reverse the string
revNumber = Val(revString) ' get the number out of the string
revNumStr = CStr(revNumber) ' convert the reversed number to a string
intNumber = CInt(StrReverse(revNumStr)) ' reverse the string back to
normal and convert to number
intNumber = intNumber + 1 ' Increment the number
revNewNumber = StrReverse(CStr(intNumber)) ' reverse the incremented
number as a string
NumLength = Len(revNumStr) 'length in digits of initial number
NumPos = InStr(revString, revNumStr)
strLeft = Left(revString, NumPos - 1)
strRight = Mid(revString, NumPos + NumLength)
strAlpha = strLeft & revNewNumber & strRight
IncrLast = StrReverse(strAlpha)
End Function

In essence, I'm reversing the string FIRST, so I can use Val to figure
out what the first (or actually last) sequence of characters that are a
number, and not rely on a dash. I then take the number, increment it,
put it all back together, then reverse it back. So far, I've
discovered two problems:

First off, when I reverse my string, AHU-20, for example, becomes
02-UHA. Val only returns 2, because it's discarding the leading 0. I
then tried it with a more complex string: AHU-2-5b. The reversed
version is b5-2-UHA, and I would THINK that Val on this would return 5,
but it's returning 0. Why is that?

I understand why the first thing is happening (but don't know how to
get around it), the second thing has me stumped!
 
P

Peter-d

It may not be the most elegant.. but this works... i used Tery's as a
starting point but changed the apporach

Function IncrLast(RHS As String) As String
Dim strLeft As String 'alpha part coming before the numeric part
Dim strRight As String 'alpha part coming after the numeric part
Dim intNumStart As Integer 'character position of the first digit of
numeric part
Dim intNumLength As Integer 'length of the numeric part
Dim intNumber As Long 'the numeric part
Dim i As Integer, j As Integer 'character counters
'loop through all character starting from the end
For i = Len(RHS) To 0 Step -1
'check if the character at this position is numeric (asci code
between 48 and 57)
If Asc(Mid(RHS, i, 1)) >= 48 And Asc(Mid(RHS, i, 1)) <= 57 Then
'We found the end of the numeric string (i)
'search for the begining of the numeric part
j = 1
Do While Asc(Mid(RHS, i - j, 1)) >= 48 And Asc(Mid(RHS, i - j,
1)) <= 57
j = j + 1
Loop
'i-j is the position of first non-numeric part we found
intNumStart = i - j + 1
intNumLength = j
intNumber = Val(Mid(RHS, intNumStart, intNumLength))
Exit For
End If
Next
'Increase the number by 1
intNumber = intNumber + 1
'Identify the part left of the string
strLeft = Left(RHS, intNumStart - 1)
'Identify the part right of the string(if any)
strRight = Mid(RHS, intNumStart + intNumLength)

'recompose the full string
IncrLast = strLeft & intNumber & strRight
End Function
 
S

standup101011

You might try padding Len(intNumber) with a string character:

Change in the existing code

strAlpha = Mid(strRight, Len(intNumber))

To

strAlpha = Mid(strRight, Len("A" & intNumber))

HTH
 
P

Peter-d

[email protected] said:
...
First off, when I reverse my string, AHU-20, for example, becomes
02-UHA. Val only returns 2, because it's discarding the leading 0. I
then tried it with a more complex string: AHU-2-5b. The reversed
version is b5-2-UHA, and I would THINK that Val on this would return 5,
but it's returning 0. Why is that?

I understand why the first thing is happening (but don't know how to
get around it), the second thing has me stumped!
Click to expand...
Val actually only returns the number contained in a string, up to the first
non numeric character. Useing val on an non numeric character autoamtically
returns a 0. And since it only look at the strign from left to right until
the first non numeric, it sees "b" and stops right there.
 
G

google

"It may not be the most elegant.. but this works... "

Given the choice, I'll take works. ;-) And it seems to work with
everything I've thrown at it so far. Now I'll have to digest it & make
sure I understand it all. Thanks so much!
 
A

Albert D.Kallal

Hum, cute problem..how about short, nice and easy to read?

Public Function IncSnum(v As Variant) As String

Dim vbuf As Variant
Dim str As String
Dim intTokens As Integer
Dim i As Integer
Dim c As String

Dim bolCFirst As Boolean
Dim strNum As String
Dim strChar As String

Dim intNumber As Integer

vbuf = Split(v, "-")
intTokens = UBound(vbuf)
str = vbuf(intTokens)
For i = 1 To Len(str)
c = Mid(str, i, 1)
If c Like "#" Then
strNum = strNum & c
Else
strChar = strChar & c
If i = 1 Then bolCFirst = True
End If
Next i
intNumber = strNum + 1
If bolCFirst = True Then
vbuf(intTokens) = strChar & intNumber
Else
vbuf(intTokens) = intNumber & strChar
End If
IncSnum = Join(vbuf, "-")

End Function

And, you can actualy read the above....

works both for
AH-1-33G, or AH-1-G33
 
B

bruce

I'm trying to implement something that would speed up data entry. I'd
like to be able to take a string, and increment ONLY the right-most
numerical characters by one. The type structure of the data that is in
this field can vary. It's a list of mechanical equipment, and how it
is designated varies based on how the customer has them labeled. For
example, a list of their equipment might look like:

CH-1
CH-2
CH-3

or it might be

AH-1-1
AH-1-2
AH-1-3

or even

FC-1-1G
FC-1-2G
FC-1-3G

And there will be times where it's even more obscure, but I'd say at
least 75% of the time, the right-most numerical part of the string will
increment like above. It seems that what I need is to somehow get the
Val function to read from right to left (it does stop at dashes,
right?), pull out that number, increment it, then shove it back in the
string. I already have my form set up so that the field automatically
populates with the value from the previous record (so they only have to
go in & edit the number to be correct instead of typing the entire
string), but what I'd like to do is also implent code on a double-click
that will take the value of that field, and do the above incrementing
(which would have the added advantage of making it easy when units are
skipped... just double-click twice).
Click to expand...

This does the trick for me (and lookie! No Val() function!):

Function IncNum(str As String) As String

Dim intNumStart As Integer
Dim intNumLen As Integer
Dim i As Integer

For i = Len(str) To 1 Step -1
If IsNumeric(Mid(str, i, 1)) Then
intNumLen = intNumLen + 1
intNumStart = i
Else
If intNumLen > 0 Then Exit For
End If
Next i

IncNum = Mid(str, 1, intNumStart - 1) & CLng(Mid(str, intNumStart,
intNumLen)) + 1 & Mid(str, intNumStart + intNumLen)

End Function

HTH,
Bruce
 
B

ByteMyzer

Hmmm, I'm still having trouble. I took yours, then re-worked it with a
different approach (some unneeded declarations are still in there):


Private Function IncrLast(RHS As String) As String
Dim intInstr As Integer
Dim strLeft As String
Dim strRight As String
Dim strAlpha As String
Dim intNumber As Integer
Dim revString As String
Dim revNumber As Integer
Dim revNumStr As String
Dim revNewNumber As String
Dim NumLength As Integer
Dim NumPos As Integer

revString = StrReverse(RHS) ' reverse the string
revNumber = Val(revString) ' get the number out of the string
revNumStr = CStr(revNumber) ' convert the reversed number to a string
intNumber = CInt(StrReverse(revNumStr)) ' reverse the string back to
normal and convert to number
intNumber = intNumber + 1 ' Increment the number
revNewNumber = StrReverse(CStr(intNumber)) ' reverse the incremented
number as a string
NumLength = Len(revNumStr) 'length in digits of initial number
NumPos = InStr(revString, revNumStr)
strLeft = Left(revString, NumPos - 1)
strRight = Mid(revString, NumPos + NumLength)
strAlpha = strLeft & revNewNumber & strRight
IncrLast = StrReverse(strAlpha)
End Function

In essence, I'm reversing the string FIRST, so I can use Val to figure
out what the first (or actually last) sequence of characters that are a
number, and not rely on a dash. I then take the number, increment it,
put it all back together, then reverse it back. So far, I've
discovered two problems:

First off, when I reverse my string, AHU-20, for example, becomes
02-UHA. Val only returns 2, because it's discarding the leading 0. I
then tried it with a more complex string: AHU-2-5b. The reversed
version is b5-2-UHA, and I would THINK that Val on this would return 5,
but it's returning 0. Why is that?

I understand why the first thing is happening (but don't know how to
get around it), the second thing has me stumped!
Click to expand...

Try:
'----------<begin code>----------
Function IncrLast(ByVal RHS As String) As String
Dim i1 As Integer
Dim i2 As Integer
Dim i3 As Integer
i2 = Len(RHS)
Do While Not IsNumeric(Mid(RHS, i2, 1))
i2 = i2 - 1
Loop
i1 = i2
Do While IsNumeric(Mid(RHS, i1, 1)) And (Mid(RHS, i1, 1) <> "-")
i1 = i1 - 1
Loop
i3 = CInt(Mid(RHS, i1 + 1, i2 - i1)) + 1
IncrLast = Left(RHS, i1) & CStr(format(i3, "0")) & Mid(RHS, i2 + 1)
End Function

'-----------<end code>-----------
 
J

John Nurick

This does the trick for me (and lookie! No Val() function!):
Click to expand...

Bruce's function fails if there's no number in the string, and also
loses any leading zeros (i.e. AB001 increments to AB2). How about this
(variant argument and return type used so it can easily be called from
a query):

Function IncNum2(V As Variant) As Variant
Dim oRE As Object 'VBScript_RegExp_55.RegExp
Dim oMatches As Object 'VBScript_RegExp_55.MatchCollection
Dim strN As String
Dim lngLenDiff As Long

If IsNull(V) Then Exit Function

Set oRE = CreateObject("VBScript.RegExp")
oRE.Pattern = "(.*?)(\d+)(\D*)$"
Set oMatches = oRE.Execute(CStr(V))
If oMatches.Count = 0 Then 'can't increment
IncNum2 = V
Else
With oMatches(0)
strN = CStr(CLng(.SubMatches(1)) + 1) 'increment
'restore any leading zeroes
lngLenDiff = Len(.SubMatches(1)) - Len(strN)
If lngLenDiff > 0 Then
strN = String(lngLenDiff, "0") & strN
End If
IncNum2 = .SubMatches(0) & strN & .SubMatches(2)
End With
End If
End Function
 
R

rkc

John said:
Bruce's function fails if there's no number in the string, and also
loses any leading zeros (i.e. AB001 increments to AB2). How about this
(variant argument and return type used so it can easily be called from
a query):

Function IncNum2(V As Variant) As Variant
Dim oRE As Object 'VBScript_RegExp_55.RegExp
Dim oMatches As Object 'VBScript_RegExp_55.MatchCollection
Dim strN As String
Dim lngLenDiff As Long

If IsNull(V) Then Exit Function

Set oRE = CreateObject("VBScript.RegExp")
oRE.Pattern = "(.*?)(\d+)(\D*)$"
Set oMatches = oRE.Execute(CStr(V))
If oMatches.Count = 0 Then 'can't increment
IncNum2 = V
Else
With oMatches(0)
strN = CStr(CLng(.SubMatches(1)) + 1) 'increment
'restore any leading zeroes
lngLenDiff = Len(.SubMatches(1)) - Len(strN)
If lngLenDiff > 0 Then
strN = String(lngLenDiff, "0") & strN
End If
IncNum2 = .SubMatches(0) & strN & .SubMatches(2)
End With
End If
End Function
Click to expand...


Fails if zero is the first character and a leading zero is in fact
required to be preserved.

099C-FF-A -> 100C-FF-A
 
T

Terry Kreft

Me being stupid shoudl be

Function IncrLast(RHS As String)
Dim intInstr As Integer
Dim strLeft As String
Dim strRight As String
Dim strAlpha As String
Dim intNumber As Integer

intInstr = InStrRev(RHS, "-", Compare:=vbBinaryCompare)

strLeft = left(RHS, intInstr)
strRight = Mid(RHS, intInstr + 1)
intNumber = Val(strRight)
strAlpha = Mid(strRight, Len(CStr(intNumber)) + 1) ' Note difference here
intNumber = intNumber + 1

IncrLast = strLeft & intNumber & strAlpha
End Function
 
M

Malcolm Cook

hows this?

Public Function IncTrailingNumeric(s As String) As String
'PURPOSE: increment the last numeric portion of input string\
'METHOD: regular expressions are your friend!
'REQUIRES: reference to ''Microsoft VBScript Regular Expressions 5.5'
'TODO: replace asserts with error raise
'EXAMPLE I/O: (past the print statement into the immediate window)
'Print IncTrailingNumeric("CH-1")
'CH-2
'Print IncTrailingNumeric("AH-1-1")
'AH-1-2
'Print IncTrailingNumeric("FC-1-1G")
'FC-1-2G
'Print IncTrailingNumeric("FC-1-19\G")
'FC-1-20\G


Dim re As New RegExp
Dim Matches As MatchCollection
Dim m As Match

With re
.Pattern = "(.*\D)(\d+)(\D*)"
Set Matches = re.Execute(s)
Debug.Assert Not Matches Is Nothing
Debug.Assert Matches.Count = 1
Set m = Matches(0)
IncTrailingNumeric = m.SubMatches(0) & (1 + Val(m.SubMatches(1))) & m.SubMatches(2)

End With
End Function
 
A

Albert D.Kallal

Well, we don't really want to add a reference to make this run...do we?
(but, nice solution by the way)

However, what about

"AH-1-G1"
"AH-1-G2"
"AH-1-G3"

A good number of solutions posted allow text on EITHER side of the number.

Without a doubt, this is GREAT little problem. Once I might keep for
interviewing new developers.....
 
G

google

Wow, thanks for all the help everyone! I was able to streamline pretty
much everything else but this field, which was the only one left that
required any mentionable keystrokes that had any potential of
automating. This will really help them fly through this application!
 
M

Malcolm Cook

Albert,

The function I coded handles your sequence just fine. input "AH-1-G1" output "AH-1-G2". n'est pas?

I use regexp all the time and thing they should be built into the language.... so, I have no problem with 'adding a reference'
since I almost always add it.

Except... the problem with adding a reference is a pain for me when i go to distibute my Access app since the Mcirosoft Access
developer extensions for packaging an app barf (technical term) on selected dlls, especiall ones which have a number, like this:

Microsoft VBScript Regular Expressions 5.5
Location: C:\WINDOWS\system32\vbscript.dll\3
Language: Standard


Regards,

Malcolm
 
D

Douglas J. Steele

You could always use Late Binding, so that no reference is required:

Public Function IncTrailingNumeric(s As String) As String
Dim re As Object
Dim Matches As Object
Dim m As Object

Set re = CreateObject("VBScript.RegExp")
With re
.Pattern = "(.*\D)(\d+)(\D*)"
Set Matches = re.Execute(s)
Debug.Assert Not Matches Is Nothing
Debug.Assert Matches.Count = 1
Set m = Matches(0)
IncTrailingNumeric = m.SubMatches(0) & (1 + Val(m.SubMatches(1))) &
m.SubMatches(2)
End With
End Function
 
Top