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

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:

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?
LVL 1
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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
``````
Regards
EE20141217.xlsm
0

Experts Exchange Solution brought to you by