Solved

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

Posted on 2014-12-16
2
219 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 49

Accepted Solution

by:
Rgonzo1971 earned 500 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

"Migrate" an SMTP relay receive connector to a new server using info from an old server.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now