How to find 3 digit number in string...

I would like to find every 3-digit number in a string and add each 3-digit number to a database.

The database stuff I know but finding a 3-digit number... I'm lost. All help is very appreciated.

Thanks
Relentless

Edit:

All the 3-digit numbers in this string are followed by 8 spaces.

I believe I can answer this for you, my question is where is the 3 digit number placed? I see it has 8 spaces after it. Is anything in front of it?

I can give you a sample code just want to make sure I have all the parts to the problem.

Doc

This is what a typical string looks like. It's from a terminal emulater program. I just want to read the Station and the Status Catagory columns. I believe it is tab delimited text.

Code:

---



VERB: CSTATUS  RETAIN? Y      SEE STATUS OF STATION GROUP

                      CURRENT  COMPLETE NO  STATUS  CATEGORY
STATION    STATUS      OPER-NO  TIME    LOTS CATEGORY DESCRIPTION

041        IDLE D LIVE FN433    10:52:05    0 PTST    SCHEDULED TEST TIME
073        IDLE D LIVE FN444    09:54:11    0 PM      PREVENTIVE MAINTENANCE (S
082        IDLE D LIVE FN432    19:41:51    0 EH      ENG TIME (SCH/UNSCH)
112        IDLE D LIVE FN207    10:08:09    0 PM      PREVENTIVE MAINTENANCE (S
153        IDLE D LIVE FN530    07:07:30    0 PM      PREVENTIVE MAINTENANCE (S
172        IDLE D LIVE FN336    09:04:25    0 PTST    SCHEDULED TEST TIME
202        IDLE D LIVE CLN002  11:44:36    0 PTST    SCHEDULED TEST TIME
226        IDLE D LIVE ETCH047  12:48:38    0 PTST    SCHEDULED TEST TIME
403        IDLE D LIVE IMP015  08:15:12    0 RT      RESPONSE TIME
502        IDLE D LIVE R101    23:54:08    0 PDT      PUMP DOWN TIME
506        IDLE D LIVE R5000    03:12:50    0 PTST    SCHEDULED TEST TIME
507        IDLE D LIVE R5000    21:47:27    0 UT      PLANT UTILITY FAILURE
508        IDLE D LIVE R7000    06:25:00    0 MT      MAINT/ REPAIR TIME (UNSCH
511        IDLE D LIVE R101    03:09:23    0 UT      PLANT UTILITY FAILURE
702        IDLE D LIVE DESCUM01 12:50:42    0 RT      RESPONSE TIME

ENTER (C) TO CONTINUE

---

Thank you for your assistance.
Relentless

This is actually relatively simple. Your text has very predictable structures.

First, take a look at what each line has in common:
"041 IDLE D LIVE FN433"

You'll notice each and every one has " IDLE D LIVE ". If any of this might be different, then don't use that part as search criteria. Only what is common to every instance. You can also use the fact that each has a specific starting position on the line. I often take advantage of the Split() and Filter() functions for things like this, and InStr() will be helpful too.

Instead of looking for the number, take each line and find the first space. That will be directly after the number you want. Now just use InStrRev() to get everything in between the space and the last vbCrLf. That will be your number, and this method will work even if the number is not three digits. Of course, you might also just take the last three characters before the space, if you can depend on three digits. An even easier way would be to use Val(), which will give you the value of the number on that line, but it won't be three digits if there are leading zero's. Format$() can fix that though. Now the rest is also easy. Just take advantage of the start position of the column following the one you want, and get everything to the left of it using Left$(). Trim$() can be used to remove any leading and trailing spaces.

BTW, you can strip out everything else before and after the lines you want, by using InStr() to get the position of "DESCRIPTION" followed by two vbCrLf's. All the lines you want are seporated by one vbCrLf, but end in two, so looking for that will find the end once you have removed the leading stuff you don't want.

Here is a way.

Code:

---


Private Sub Command1_Click()

  Dim TextLine$, Filename$
  Dim File As Integer

  Filename$ = "C:\Documents and Settings\student\Desktop\test.txt"

    ' Test if the file exists
  If Dir(Filename$) = "" Then Exit Sub

  File = FreeFile ' This is safer than assigning a number

  Open Filename$ For Input As #File

  Do While Not EOF(File)        ' Loop until end of file
        Line Input #File, TextLine$  ' Read line into variable
        If IsNumeric(Left(TextLine, 1)) Then
            List1.AddItem Left(TextLine, 5) & "  " & Mid(TextLine, 46, 10)
        End If
  Loop

  Close #File

End Sub

---

Wow, both your suggestions are very slick (nice). I'll try both. I also found this in the VBA program I'm controlling... It looks promising also:

Text = object.GetText(StartRow, StartColumn, EndRow, EndColumn, [Options])

Code:

---

For I = 0 To 15
    displaytext = SPNSessionF1.GetText(7 + I, 0, 7 + I, 49)
    FoundNumber = Left(displaytext, 3) & " " & Right(displaytext, 4)
    'Write to Database Here
Next I

---

Thank to all for the suggestions.
Rel