Results 1 to 7 of 7

Thread: Search List of my codes

  1. #1
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    14

    Search List of my codes

    Hi

    In one cell I have the following text:

    H12345 West Atlantic (GMDSS Metarea IV) 0430z 1030z 1630z 2230z
    H123456 Tropical Atl. 0415z 1015z 1615z 2215z
    Tips H11111 East Pacific (GMDSS Metarea XII) 0545z 1145z 1745z 2345z
    H022220 NE Pacific 0430z 1030z 1630z 2230z
    Hi Lo not H033333 N. Mid-Pacific 0445z 1045z 1645z
    H44444 Mexico highseas 0400z 1000z 1600z 2200z
    New H50500 S. Mid-Pacific 0515z 1115z 1715z 2315z
    H012345 Peru Highseas (GMDSS Metarea XVI) 0515z

    How to filter the following items: (search string = 1 letter + 5/6 numbers)

    H12345
    H123456
    H11111
    H022220
    H033333
    H44444
    H50500
    H012345

    I am looking for suggestions

  2. #2
    Junior Member
    Join Date
    Aug 2014
    Posts
    10
    Rep Power
    0
    Are all 8 lines in a single cell or is that 8 rows of data?

  3. #3
    Junior Member
    Join Date
    Aug 2014
    Posts
    10
    Rep Power
    0
    If 8 rows, the following UDF will do that:

    Code:
    Function GetHCode(r As String) As String
    With CreateObject("vbscript.regexp")
        .Pattern = "H[0-9]{5,6}"
        If .test(r) Then GetHCode = .Execute(r)(0)
    End With
    End Function

  4. #4
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    14
    Hi,

    https://sites.google.com/site/e90e50/vbscript/regexp
    RegExp great the first entry is correct!

    Unfortunately I can not remove the following entries in the text
    I may replace the first string found with "" to search again

    You have to find all the results in the cell

  5. #5
    Junior Member
    Join Date
    Aug 2014
    Posts
    10
    Rep Power
    0
    Code:
    Sub GetAllHCodes()
    Dim m As Object, s As String, a, n As Long
    s = Range("A1")
    With CreateObject("vbscript.regexp")
        .Pattern = "H[0-9]{5,6}"
        .Global = True
        If .test(s) Then
            Set m = .Execute(s)
            For Each a In m
                n = n + 1
                Cells(n, 2) = a
            Next
        End If
    End With
    End Sub

  6. #6
    Senior Member
    Join Date
    Oct 2011
    Posts
    135
    Rep Power
    14
    Hi

    Very well
    thank you very much

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by Scott Huish View Post
    Code:
    Sub GetAllHCodes()
    Dim m As Object, s As String, a, n As Long
    s = Range("A1")
    With CreateObject("vbscript.regexp")
        .Pattern = "H[0-9]{5,6}"
        .Global = True
        If .test(s) Then
            Set m = .Execute(s)
            For Each a In m
                n = n + 1
                Cells(n, 2) = a
            Next
        End If
    End With
    End Sub
    For those who might be interested, you can produce the same results as the above code without using RegExp...

    Code:
    Sub GetAllHCodes()
      Dim X As Long, HCodes As String, H() As String
      H = Split(Range("A1").Value, "H")
      For X = 1 To UBound(H)
        If Not Split(H(X))(0) Like "*[!0-9]*" And InStr(H(X), " ") > 4 Then HCodes = HCodes & " H" & Split(H(X))(0)
      Next
      H = Split(Trim(HCodes))
      Range("B1:B" & UBound(H) + 1) = Application.Transpose(H)
    End Sub

Similar Threads

  1. Search Directories to List Files VBA
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 3
    Last Post: 04-15-2014, 08:22 PM
  2. Is this codes be translated into Excel?
    By mrprofit in forum Excel Help
    Replies: 1
    Last Post: 04-11-2014, 11:49 PM
  3. Replies: 0
    Last Post: 07-24-2013, 11:20 PM
  4. Search and remove values ​​from a list
    By PcMax in forum Excel Help
    Replies: 4
    Last Post: 04-14-2013, 08:39 PM
  5. Correlation Map with color codes
    By Rasm in forum Excel Help
    Replies: 5
    Last Post: 12-04-2011, 08:28 PM

Posting Permissions

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