string manipulation

[shadow314us]

Hi all ,

I have a simple question I am working with a string that looks like

" 1/2X014 AB tape". I want to pull out AB from the string and run a query. The length of the string changes from time to time but I want to keep track of two alphabets.

Need ur inputs on this ....

[dglienna]

If there is always a space before and after, you can just use Split()

Option Explicit
 
Private Sub Form_Load()
  Dim str() As String
  Dim a As String
  a = Trim$(" 1/2X014 AB tape") ' deletes leading and trailing spaces
  str() = Split(a, " ")
  MsgBox str(1)
End Sub

[shadow314us]

iam working in VBA environment and the split function is throwing me an exception as sub or function not defined.

need ur thoughts on this ..... thanks

[si_the_geek]

There is a Split function here that you can use in VBA.

[dglienna]

This should work just as well.

Option Explicit
 
Private Sub Form_Load()
  Dim str() As String
  Dim a As String, sStart As Integer, sEnd As Integer
  a = Trim$(" 1/2X014 AB tape") ' deletes leading and trailing spaces
  sStart = InStr(1, a, " ")
  sEnd = InStr(sStart + 1, a, " ")
  MsgBox Mid$(a, sStart, sEnd - sStart)
End Sub

[MartinLiss]

Moved.