Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel how to find substring match, then extract numbers in the string.

Posted on 2014-12-16
2
Medium Priority
?
269 Views
Last Modified: 2014-12-17
I have some complicated two step process need to be done in my work. I have two tables, one for email address and another for email header information. If there's email address match, then I extract IP address from the header information.

For example,

Table A:
aaa@xyz.com
bbb@xyz2.com

Table B:
asdfasdfasdfadsf aaa@xyz.com sadfasfdsafa 111.222.333.444 asdfasdf asdf asdf
asdfasdf bbb@xyz2.com 3223sadadsf sadfas32423 444.222.555.777 2432423113sadfadfasdfads

Both table has only one column.
Now the returned values should be 1111.222.333.444 and 444.222.555.777. Then either insert the two IP addresses to 2nd column of Table A or B or open with a new table.

How can I do that?
0
Comment
Question by:crcsupport
2 Comments
 
LVL 53

Accepted Solution

by:
Rgonzo1971 earned 2000 total points
ID: 40504209
Hi,

pls try this User defined function

Function FindIP(strFind As String, rng As Range) As String
Found = False
For Each c In rng
    If c.Text Like "*" & strFind & "*" Then
        Found = True
        result = ExtractIP(c.Text)
        Exit For
    End If
Next
If Found = False Then
    FindIP = ""
Else
    FindIP = result
End If
End Function

Function ExtractIP(strText As String) As String
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.Pattern = "[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}"
RE.Global = True
RE.IgnoreCase = True
Set allMatches = RE.Execute(strText)

If allMatches.Count <> 0 Then
    result = allMatches.Item(0).Value
End If

ExtractIP = result


End Function

Open in new window

Regards
EE20141217.xlsm
0
 
LVL 1

Author Comment

by:crcsupport
ID: 40504858
BEST
0

Featured Post

WatchGuard Case Study: NCR

With business operations for thousands of customers largely depending on the internal systems they support, NCR can’t afford to waste time or money on security products that are anything less than exceptional. That’s why they chose WatchGuard.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question