Comparable InStrRev function in VBA? 1

[stnkyminky]

I've got a string, ex. xxx,yyy,zzzz,10.50 I need to strip the 10.50 off of the string. In VB I would use InStrRev (searches the string from back to front) to get the position of the last comma. Is there a comparable function in VBA to do that has the same functionality? Scott
Programmer Analyst
<><

 

[scking]

No. You need to write a function using loops and InStr to find the last occurence. It's not that difficult but it is different.

Steve King Growth follows a healthy professional curiosity

 

[SgtJarrow]

If the format is always xxx,yyy,zzzz,10.50 or something similar, how using a Left() function to just get what you need. You will be dropping the last six characters every time so....

Left(StringName, Len(StringName)-6)

Should do the trick....that will start from the left and continue until six from the end of the string..... Programming isn't a profession of choice.
It's a profession of calling...
&quot;Hey Programmer, your application broke again!&quot;

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer

 

[scking]

See below. This took less than 10 min to build so sometimes it's easier to build your own than try to locate the intrinsic function from other apps.

Testing results:
?StripFromLastPosition(&quot;Find.Me.The.Last.Occurence.of.the.period&quot;, &quot;.&quot
Find.Me.The.Last.Occurence.of.the

Public Function StripFromLastPosition(MyString As String, _
strSearch As String) As String

Dim intLen As Integer
Dim intPos As Integer
Dim intLastFound As Integer
Dim strResult As String

intLen = Len(MyString)
For intPos = intLen To 1 Step -1
If Mid$(MyString, intPos, 1) = strSearch Then
intLastFound = intPos
Exit For
End If
Next intPos

strResult = Left$(MyString, intLastFound - 1)
StripFromLastPosition = strResult

End Function

Steve King Growth follows a healthy professional curiosity

 

[scking]

The above function will only work with a single search character but could easily be extended to search for a string <= the length of the original string.

Public Function StripFromLastPosition(MyString As String, _
strSearch As String) As String

Dim intLen As Integer
Dim intPos As Integer
Dim intLastFound As Integer
Dim strResult As String
Dim intSearchSize As Integer

intLen = Len(MyString)
intSearchSize = Len(strSearch)
intLen = intLen - intSearchSize + 1

For intPos = intLen To 1 Step -1
If Mid$(MyString, intPos, intSearchSize) = strSearch Then
intLastFound = intPos
Exit For
End If
Next intPos

If intLastFound > 1 Then
strResult = Left$(MyString, intLastFound - 1)
Else
strResult = &quot;&quot;
End If
StripFromLastPosition = strResult

End Function

Steve King Growth follows a healthy professional curiosity

 

[stnkyminky]

Yeah, typically I post the problem then try to work it out.
Below is the solution I created before rcving yours.

BTW Also, I wanted to keep the 10.5 from the string not discard it. I didn't explain what I wanted....my bad.

Anyway thanks for the solution.


public function getlength(Description as string) as double
Dim length As String
Dim lenDesc As Long
Dim i As Long
Dim currpos As Long
Dim oldpos As Long
Dim begtime As Single
Dim endtime As Single

lenDesc = Len(description)
oldpos = 0
For i = 1 To lenDesc
currpos = InStr(i, description, &quot;,&quot
If oldpos < currpos Then oldpos = currpos
Next

oldpos = oldpos + 1
length = Mid(description, oldpos)

getlength = CDbl(length)
End Function Scott
Programmer Analyst
<><