Results 1 to 12 of 12

Search and Extract text from a list of description

Threaded View

  1. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,834

    Re: Search and Extract text from a list of description

    Why Federal Express; FedEx in row 3?
    Sub test()
        Dim a, i As Long, myPtn As String, m As Object, dic As Object
        Set dic = CreateObject("Scripting.Dictionary")
        dic.CompareMode = 1
        With Sheets("client")
            myPtn = Join(Application.Transpose(.Range("a2", .Range("a" & Rows.Count).End(xlUp)).Value), Chr(2))
        End With
        With Cells(1).CurrentRegion.Resize(, 2)
            .Columns(2).Offset(1).ClearContents
            a = .Value
            With CreateObject("VBScript.RegExp")
                .Global = True
                .Pattern = "([$()^\-|{}\[\]+*?.])"
                myPtn = Replace(.Replace(myPtn, "\$1"), Chr(2), "|")
                .Pattern = "\b(" & myPtn & ")\b"
                For i = 2 To UBound(a, 1)
                    For Each m In .Execute(a(i, 1))
                        dic(m.Value) = Empty
                    Next
                    If dic.Count Then a(i, 2) = Join(dic.keys, "; ")
                    dic.RemoveAll
                Next
            End With
            .Value = a
        End With
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 02-20-2014, 12:24 PM
  2. VBA: Search text and extract hyperlink in same cell
    By sroysroy in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-26-2013, 11:59 AM
  3. Search/extract text
    By Plasma in forum Excel General
    Replies: 3
    Last Post: 01-17-2012, 12:16 PM
  4. [SOLVED] search and extract within text
    By sunnyday in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-06-2005, 10:05 PM
  5. Replies: 9
    Last Post: 08-31-2005, 12:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1