How to extract text within a string of text

by

Here is a very flexible VBA answer using the regex object. What the function does is extract every single sub-group match it finds (stuff inside the parenthesis), separated by whatever string you want (default is “, “). You can find info on regular expressions here: http://www.regular-expressions.info/

You would call it like this, assuming that first string is in A1:

=RegexExtract(A1,"gi[|](\d+)[|]")

Since this looks for all occurance of “gi|” followed by a series of numbers and then another “|”, for the first line in your question, this would give you this result:

297848936, 297338191

Just run this down the column and you’re all done!

Function RegexExtract(ByVal text As String, _
                      ByVal extract_what As String, _
                      Optional separator As String = ", ") As String

Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
Dim i As Long, j As Long
Dim result As String

RE.pattern = extract_what
RE.Global = True
Set allMatches = RE.Execute(text)

For i = 0 To allMatches.count - 1
    For j = 0 To allMatches.Item(i).submatches.count - 1
        result = result & (separator & allMatches.Item(i).submatches.Item(j))
    Next
Next

If Len(result) <> 0 Then
    result = Right$(result, Len(result) - Len(separator))
End If

RegexExtract = result

End Function

More Related Contents:

  1. Want to concatenate multiple row data in in cell along with its values if present. Emp Id should not come if no data available infromt of it
  2. Excel Number Separation [closed]
  3. Excel formula checklist in employees
  4. Sequence of numbers based on start and end value of 2 cells [closed]
  5. Error in finding last used cell in Excel with VBA
  6. Excel VBA – How to add dynamic array formula
  7. Filling any empty cells with the value above
  8. Find last used cell in Excel VBA
  9. Rounding in MS Access
  10. Count unique values in Excel
  11. Concatenate column headers if value in rows below is non-blank
  12. How do I apply a SumIf Formula in a VBA Sub? Getting Error 1004
  13. Countif With Multiple OR Criteria
  14. How to roundup a number to the closest ten?
  15. I have an Excel data sheet that I would like to extract data from into a new sheet
  16. Getting unique values in Excel by using formulas only
  17. How to add a named sheet at the end of all Excel sheets?
  18. VBA general way for pulling data out of SAP [closed]
  19. Delete Row based on Search Key VBA
  20. How to add a button programmatically in VBA next to some sheet cell data?
  21. How can I kill task manager processes through VBA code?
  22. How to add parameters to an external data query in Excel which can’t be displayed graphically?
  23. Optimizing Excel formulas – SUMPRODUCT vs SUMIFS/COUNTIFS
  24. Fill non-contiguous blank cells with the value from the cell above the first blank
  25. How to send mail when the .Send does not work?
  26. How do I click a link on a web page using Excel VBA?
  27. Create hyperlink to another sheet
  28. Convert Early Binding VBA to Late Binding VBA : Excel to Outlook Contacts
  29. How to protect Excel workbook using VBA?
  30. Unique Rank value for a subgroup within a group

Leave a Comment