Extracting characters in a string with VBA

[Scatman]

I have a need to label features in a GIS system based off of a field in a table. The field name is TapNumber and here is how the information looks:
10204001-10313001-91L+110
19205020-19205002-7R+021
8016014-8016011-143R+143

What I need is only the numbers between the second "-" and the "L" or "R"

I should also tell you the string length is not consistent. I already tried to do a mid function to the 19th character, but unfortunately the string length varies as you can see from the examples above.

Any ideas?

Thanks

 

[MarkK]

Does the string length vary from the right, and if not you can use the 'Right()' function and/or the 'InStrRev()' function.

 

[dkinley]

Just for an idea, something like ....

Dim sNumber As String
Dim iFirstPositionNum As Long
Dim iLastPositionNum As Long
 
sNumber = Your Input Number
 
iFirstPositionNum = InStr(sNumber, "-") + 1
iLastPositionNum = InStr(2, sNumber, "L")
If iLastPositionNum = -1 Then
    iLastPositionNum = InStr(2, sNumber, "R")
End If
Your Output = Mid(sNumber, iFirstPositionNum, Abs(iLastPositionNum - iFirstPositionNum))

Again, this is aircode - not tested and I am sure that some better coders know some better functionality that can shrink this up.

-dK

 

[DCrake]

Simple Software Solutions

Here is my solution

Public Function ParseCode(AnyCode As String) As String

Dim iHash As Integer   'position of the last (second) hyphen
Dim iChar As Integer   'position of the character L or R
Dim iLong As Integer   'Number of characters between the two

iHash = InStrRev(AnyCode,"-")

'Test for a hyphen in the code
'If not must be an invalid code
'Return invalid code and exit

If iHash = 0 Then
   ParseCode = AnyCode
   Exit Function
End If

If InStr(AnyCode,"L") Then
   iChar = InStr(AnyCode,"L")
Else If InStr(AnyCode,"R" Then
   iChar = InStr(AnyCode,"R")
Else
   iChar = 0
End If

'Test for a L or an R in the code
'If not must be an invalid code
'Return invalid code and exit

If iChar = 0 Then
   ParseCode = AnyCode
   Exit Function
End If

'Calculate the length of the code to be extracted
iHash = iHash + 1
iChar = iChar -1
iLong = (iChar - iHash)


'Rules dictate that the L or R must be after the last hyphen
'If not then an invalid code
'Return invalid code and exit

If iLong < 1 Then
   ParseCode = AnyCode
   Exit Function
End If

'Extract the required element from the code
'Return the value
ParseCode = Mid(AnyCode,iHash,iLong)

End Function

Again this is aircode and has not been validated


To run this function place in a module and save
From the Immediate window type in
? Parsecode("10204001-10313001-91L+110")

Return value = 91

CodeMaster:

 

[Scatman]

Thanks DCrake. With a little modification, I was able to make this work in the ArcGIS 9.2 software. For those folks that just need to see the final solution, here is my working code:

Function FindLabel ( [TAPNUMBER] )
ihash=InStrRev([TAPNUMBER],"-")
If InStr([TAPNUMBER],"L")>0 Then
ichar= InStr([TAPNUMBER],"L")
Else
ichar=InStr([TAPNUMBER],"R")
End If
ihash=ihash+1
ilong=ichar-ihash
FindLabel = Mid([TAPNUMBER],ihash,ilong)
End Function