STRING

[Kendel]

Hi,

How can I get part of the string that is vary in length?
The string looks like:
" .... ........... ........ for CNNii on ...................

========= blab blab blab @#$5 --g --all kinds of characters..
............. "

How can I get the CNNii ? CNN is followed by 2 or 3 number. It can be CNN01 or CNN789 ..... CNNii is always between "for" & "on"

for CNNii on

Is it possible to get CNNii???

Thanks a bunch.

Kendel

 

[Foada]

This should get you started

Private Sub Command1_Click()
Dim s As String
Dim iStart As Long
Dim iEnd As Long

s = "khFor CNN23 ON jaf;hdfhdalf"
iStart = InStr(1, s, "CNN", vbBinaryCompare)
iEnd = InStr(1, s, "ON", vbBinaryCompare)
s = Mid$(s, iStart, (iEnd - iStart))
Print s
End Sub

Hope this helps. If you choose to battle wits with the witless be prepared to lose.

 

[Kendel]

Thank you foada!

 

[lavomar1]

For the iEnd, I'll start the search from CNN
and add a trim to remove the trailing space :

Private Sub Command1_Click()
Dim s As String
Dim iStart As Long
Dim iEnd As Long

s = "khFor CNN23 ON jaf;hdfhdalf"
iStart = InStr(1, s, "CNN", vbBinaryCompare)
iEnd = InStr(iStart, s, "ON", vbBinaryCompare)
s = Mid$(s, iStart, (iEnd - iStart))
Trim(s)
Print s
End Sub

 

[Foada]

Sorry about that lavomar1, I meant to do that but I just plain forgot to type in that line. If you choose to battle wits with the witless be prepared to lose.

 

[Kendel]

Thanks for all the inputs guys.

 

[CajunCenturion]

This would also be a good use of the Regular Expression Object. The regular expression also takes into account the existence of the either 2 or 3 digits following the CNN, which are not taken into account in the InStr solutions.

Dim MyRegExp As RegExp
Dim MatchCol As MatchCollection
Dim OneMatch As Match
Dim CNNVal As String
Dim TheString As String

TheString = "blah blah-- for CNN123 and rest of string"
Set MyRegExp = New RegExp
MyRegExp.Pattern = "(\sCNN\d{2}\s|\sCNN\d{3}\s)"
Set MatchCol = MyRegExp.Execute(TheString)
If (MatchCol.Count > 0) Then
CNNVal = Trim(MatchCol.Item(0).Value)
Else
CNNVal = vbNullString
MsgBox "No CNN Match Found"
End If
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[CajunCenturion]

Posted the wrong pattern, should be

MyRegExp.Pattern = "\sCNN\d{2,3}\s"

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein