Solved

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

Posted on 2014-12-16
2
229 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 50

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

The Nano Server Image Builder helps you create a custom Nano Server image and bootable USB media with the aid of a graphical interface. Based on the inputs you provide, it generates images for deployment and creates reusable PowerShell scripts that …
A recent project that involved parsing Tableau Desktop and Server log files to extract reusable user queries for use in other systems. I chose to use PowerShell to gather the data, and SharePoint to present it...
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

756 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