Help: Searching and extracting data from rows

[sarah]

Hi All,


I need help with a large file consisting of thousands of rows and one column, the data does not have a defined format so each entry is different. However, there is a common occurrence in those rows and I'd like to extract them. For example:
Cell A1:
lariahfi area gfdgfd aevfgre rgde 127.1.1.0 afewa 1fdsa 00:00:00:13:11:3d afd aea

Cell A2:
agrda gfr aaa 127.0.1.42 afda 11:11:11:1f:ff:ff dawfw ged

Cell A3:
agdad 1.1.1.1 afewa gvr 31:42:13:12:12:12 af

I'd like to be able to pull the following output:
Column B:
127.1.1.0
127.0.1.42
1.1.1.1


Column C:
00:00:00:13:11:3d
11:11:11:1f:ff:ff
31:42:13:12:12:12

Any help is greatly appreciated.

Thanks!

[alansidman]

Have not figured how to do the IP addresses but here is the formula for column C

=MID(A1,FIND(":",A1)-2,17)

[LalitPandey87]

UDF will make your job easy. Do you want macro?

[LalitPandey87]

Here is UDF you can use:

Function LMP_ExtractValue1(ByVal Text As Range)


    Dim RegExp              As Object
    Dim allMatches          As Object
    Dim Result              As String
    
    Set RegExp = CreateObject("vbscript.regexp")
    RegExp.Pattern = ".*?(\d{1,3}(\.\d{1,3}){3})|.*"
    Set allMatches = RegExp.Execute(Text)
    If LenB(Trim(Text)) > 0 Then
        If allMatches.Count <> 0 Then
            Result = allMatches.Item(0).submatches.Item(0)
        End If
    Else
        Result = CVErr(xlErrValue)
    End If
    
    LMP_ExtractValue1 = Result


End Function

How to use:

=LMP_ExtractValue1(A1)

Function LMP_ExtractValue2(ByVal Text As Range)


    Dim RegExp              As Object
    Dim allMatches          As Object
    Dim Result              As String
    
    Set RegExp = CreateObject("vbscript.regexp")
    RegExp.Pattern = "((\:[0-9,A-Z,a-z]|[0-9,A-Z,a-z]{1,2}){11})"
    Set allMatches = RegExp.Execute(Text)
    If LenB(Trim(Text)) > 0 Then
        If allMatches.Count <> 0 Then
            Result = allMatches.Item(0).submatches.Item(0)
        End If
    Else
        Result = CVErr(xlErrValue)
    End If
    
    LMP_ExtractValue2 = Result


End Function

How to use:

=LMP_ExtractValue2(A1)