Solved

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

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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

831 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