Extracting a text string from sheet VBA

[fari1]

Hi,
I've sheet, that has the data in Column A and in big text strings, i want to extract the following out of the sheet.

the text string starting from Archives and ending at index.htm and pasting it in sheet2 and same goes on for the whole sheet, whereever in the sheet it finds archives, it must copy data from there till index.htm and paste them after the used line.

e.g

 Archives/abd/data/1050915/000095012311073814/0000950123-11-073814-index.htm" href="/Archives/abc/data/1050915/000095012311073814/00009501

i want to get this, out of above lineArchives/abc/data/1050915/000095012311073814/0000950123-11-073814-index.htm

 

[Rick Rothstein]

Give this macro a try (run it with your data sheet as the active sheet)...

Sub GetIndexFilePaths()
  Dim X As Long, LastRow As Long, OutputRow As Long, CellContent As String
  Const StartRow As Long = 1
  Const DataCol As String = "A"
  Const OutputSheet As String = "Sheet2"
  Const OutputCol As String = "A"
  LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
  OutputRow = Worksheets(OutputSheet).Cells(Rows.Count, OutputCol).End(xlUp).Row
  For X = StartRow To LastRow
    If LCase(Cells(X, DataCol).Value) Like "*href=""/archives/*index.htm*" Then
      OutputRow = OutputRow + 1
      CellContent = Cells(X, DataCol).Value
      Worksheets(OutputSheet).Cells(OutputRow, OutputCol).Value = Split(Mid(CellContent, InStr(1, CellContent, "href=""/archives/", vbTextCompare) + 6), """>")(0)
    End If
  Next
End Sub

 

[fari1]

oh man, u'r the best, its working like best, just one thing now,can u amend to work on sheet "wquery" only, i mean on whatever sheet i'm it must work on run on that sheet only

 

[fari1]

i meant to say data sheet

 

[Rick Rothstein]

I wasn't completely sure which sheet you were refering to, so I modified my code to allow you to set everything the way you need it via the constant (Const) statements. The StartRow, DataSheet and DataCol constants are for the sheet with your text whereas OutputSheet and OutputCol are for the destination sheet. Once you set those constants up, you will be able to run the macro from any sheet in the workbook.

Sub GetIndexFilePaths()
  Dim X As Long, LastRow As Long, OutputRow As Long, CellContent As String
  Const StartRow As Long = 1
  Const DataSheet As String = "Sheet1"
  Const DataCol As String = "A"
  Const OutputSheet As String = "Sheet2"
  Const OutputCol As String = "A"
  LastRow = Worksheets(DataSheet).Cells(Rows.Count, DataCol).End(xlUp).Row
  OutputRow = Worksheets(OutputSheet).Cells(Rows.Count, OutputCol).End(xlUp).Row
  For X = StartRow To LastRow
    If LCase(Worksheets(DataSheet).Cells(X, DataCol).Value) Like "*href=""/archives/*index.htm*" Then
      OutputRow = OutputRow + 1
      CellContent = Worksheets(DataSheet).Cells(X, DataCol).Value
      Worksheets(OutputSheet).Cells(OutputRow, OutputCol).Value = Split(Mid(CellContent, InStr(1, CellContent, "href=""/archives/", vbTextCompare) + 6), """>")(0)
    End If
  Next
End Sub

 

[Rick Rothstein]

I just reread you message and think I may have misunderstood what you wanted in my last reply. Are you saying you have more than one sheet to process with your htm code on it and that you want to be able to go to a specific sheet and run the code against that sheet? If so, try this version of my macro instead (it removes the DataSheet constant and will process the text on the ActiveSheet only, still putting the output to the designated output sheet that you assigned to the OutputSheet constant)...

Sub GetIndexFilePaths()
  Dim X As Long, LastRow As Long, OutputRow As Long, CellContent As String
  Const StartRow As Long = 1
  Const DataCol As String = "A"
  Const OutputSheet As String = "Sheet2"
  Const OutputCol As String = "A"
  LastRow = ActiveSheet.Cells(Rows.Count, DataCol).End(xlUp).Row
  OutputRow = Worksheets(OutputSheet).Cells(Rows.Count, OutputCol).End(xlUp).Row
  For X = StartRow To LastRow
    If LCase(ActiveSheet.Cells(X, DataCol).Value) Like "*href=""/archives/*index.htm*" Then
      OutputRow = OutputRow + 1
      CellContent = ActiveSheet.Cells(X, DataCol).Value
      Worksheets(OutputSheet).Cells(OutputRow, OutputCol).Value = Split(Mid(CellContent, InStr(1, CellContent, "href=""/archives/", vbTextCompare) + 6), """>")(0)
    End If
  Next
End Sub

 

[fari1]

hi rick, i meant to say, that i have this data in my data sheet and i run this code on this sheet only.say for example i'm on sheet3 or sheet4 of any other sheet of my workbook, and i want that whenever i run this code, it process only the data automatically, i dun have to go there to run this code

 

[fari1]

amended it myself, thanks rick