Search text file and return values

I have a text file and want to return some values. The text file is actually a web page. Each name has 14 attributes and the information is enclosed in this text <span style='background-color:#425527;color:#ffffff;width:15px;display:block;'>8 I looking to pull the back ground color reference into a sheet. Some times there is no back ground color <span style='color:#ffffff;width:15px;display:block;'>2 I would just like to return N/A or something.

All color references need to be returned in order so the first name C. Hill should have 14 color references ideally from B2:O2 then the next name from B3:O3 and so forth

I guess I need to do some kind of string search? VB knowledge is limited so any help would be greatly appreciated.

I enclose the text file and example sheet on how I'd like the data to be displayed.

Thank you kindly for any help.

hi, skyblues, please check attachment, run code "test", hope this helps

Hello skyblues,

Even though this a late post, you still may find this interesting. This macro extracts all the players' names and the color values for each one. The macro uses Regular Expressions to parse the data from the HTML code. The attached workbook has the macro added to it. The code is commented to help you follow and understand the parsing process.

Sub ParseTextFile()

 ' Thread: http://www.excelforum.com/excel-programming/788303-search-text-file-and-return-values.html
 ' Poster: skyblues
 ' Author: Leith Ross
 
  Dim Cnt As Long
  Dim Data As Variant
  Dim DataEnd As Long
  Dim FilePath As String
  Dim FSO As Object
  Dim N As Long
  Dim Player As String
  Dim R As Long
  Dim RegExp As Object
  Dim S As Long
  Dim StringData As String
  Dim Text As String
  Dim TextFile As Object
  
    FilePath = Application.GetOpenFilename("Text Files (*.txt), *.txt")
    If FilePath = "False" Then Exit Sub
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    ' Open the text file and all the data as a single string
      Set TextFile = FSO.OpenTextFile(FilePath, 1, False)
        StringData = TextFile.ReadAll
      TextFile.Close
  
    Set RegExp = CreateObject("VBScript.RegExp")
    RegExp.Pattern = "\>([\w\.\s\-]+)<\/a\>"

    
    ' Get players names
      Do While RegExp.Test(StringData)
         Set Match = RegExp.Execute(StringData)
         Player = Match(0).SubMatches(0)
         
         ' Get the starting index and length of player within the file's text
           S = Match(0).FirstIndex + 1
           N = Match(0).Length
           
         ' Was the player's name parsed?
           If Match(0).Length > 0 Then
              StringData = RegExp.Replace(StringData, String(N, "*"))
              DataEnd = InStr(S, StringData, "onmouseout='clearToolTipBox()")
            
            ' Is there any more data?
              If DataEnd = 0 Then Exit Do
              
            ' Substring following the palyer that should contain the background values
              BackColorData = Mid(StringData, S + N, DataEnd - (S + N))
              
             ' Save the Player's name
               ReDim Data(0)
               Data(0) = Player
               Cnt = Cnt + 1
               
              ' Parse BackgroundColor Values for the player
                RegExp.Pattern = "\<span style='(background\-color\:|color\:)(\#\w{6})\;"
                
                Do While RegExp.Test(BackColorData)
                  Set Match = RegExp.Execute(BackColorData)
                  N = Match(0).Length
                  
                  If N > 0 Then
                     Select Case Match(0).SubMatches(0)
                       Case Is = "color:"
                         ' Add #N/A to cell
                         ColorValue = "#N/A"
                       Case Is = "background-color:"
                         ' Add color value to cell
                         ColorValue = Match(0).SubMatches(1)
                     End Select
                     
                   ' Expand the data array to include the new data
                     ReDim Preserve Data(Cnt)
                     Data(Cnt) = ColorValue
                     Cnt = Cnt + 1
                    
                   ' Overwrite the matched data with asterisks to prevent matching a second time
                     BackColorData = RegExp.Replace(BackColorData, String(N, "*"))
                  End If
                Loop
           End If
          
       ' Copy the data to the worksheet
         Range("A2").Offset(R, 0).Resize(1, Cnt).Value = Data
         
       ' Reset the counters, clear the player data, and change the parsing pattern back for the next player
         Cnt = 0                                    'Column counter
         R = R + 1                                  'Row offset from "A2"
         Erase Data                                 'Clear the Data array
         RegExp.Pattern = "\>([\w\.\s\-]+)<\/a\>"   'Pattern to parse player name
      Loop
   
End Sub

Hello skyblues,

When you get back, if there is anything you don't understand or if something isn't working correctly then let me know.