Stripping away unwanted characters in strings

[liltechy]

I need help with VBA code to strip away unwanted characters for a field in a table that was imported from Excel to Access97. Here is a sample of the data:

GEMS #377 - Daily intercompany outgoing invoicing acknowledgements


This is what I need it to be:
377


I would appreciate any help with this.


liltechy

 

[oharab]

Are your numbers always going to be in the same format?
If so then use this function:

Function GetBetween(strText As String, chrStart As String, chrEnd As String) As String
Dim intStart As Integer, intEnd As Integer
intStart = InStr(strText, chrStart)
intEnd = InStr(intStart + 1, strText, chrEnd)
If intStart > 0 And intEnd > 0 Then
GetBetween = Trim(Mid(strText, intStart + 1, intEnd - intStart - 1))
Else
GetBetween = ""
End If
End Function

Call it with the full string, then the delimeters to mark what you want eg:
GetBetween("GEMS #377 - Daily intercompany outgoing invoicing acknowledgements","#","-&quot

If it can't find either delimeter, or they are in the wrong order, then it returns an empty string.

Let me know if this works.

Ben ----------------------------------
Ben O'Hara
bo104@westyorkshire.police.uk
----------------------------------