Extracting multiple email address from excel cell /excel file

debugcn Published at Dev

Matt

Is there a way to extract multiple email address from excel cell /excel file

    |   A   |   B                                                                  
----|-------------
1   |HTML   |
    |Content|
    |   1   |
----|-------|-----
2   |HTML   |
    |Content|
    |   2   |
----|-------|-----
N Rows|     |

More or less the content is the same in columns

HTML Content in column one row one

cid=9498&peid= **[email protected]** &mbrid=cf7cc75d395cbf901f73967b1267db42 style="text-decoration: none;  color: blue;">Update</a> contact/membership info.<font style="font-size:14px" face="Verdana" color="#000000">&nbsp;&nbsp;|&nbsp;&nbsp;<font style="font-size:12px" face="Verdana" color="#3c3c3c"><a href=https:///fe_web_member_subscription_verify.php?oeid= 
**[email protected]**&cid=9498 style="text-decoration: none;  color: blue;">Subscribe</a> to receive requirements & resumes
                                                                    </font>        
                                                            </p>
                                                       </td>
                                            </tr>

                              <tr>
                                                   <td colspan="2"  valign="middle">
                                                            <p style="margin-left:0px;margin-right:0px;margin-top:0px; margin-bottom:0px;"> 
                                                                    <font style="font-size:15px" color="#3C3C3C" face="Verdana" >
                                        <br><u>From</u>:<br>
                                               Navee ,<br>
                                               Centra<br>                                            
                                                                           [email protected]<br>
                                                                           <span style="background-color: #FFFF00"><b>Reply to:</b></span> &nbsp;&nbsp;[email protected]<br><br>

                                                                    </font>        
                                                            </p>
                                                  </td>
                                            </tr>  

                                                  <tr>
                                                    <td  colspan="2" valign="middle">
                                                            <br><p><strong>Big

HTML Content in column one row two

cid=9498&peid= **[email protected]** &mbrid=cf7cc75d395cbf901f73967b1267db42 style="text-decoration: none;  color: blue;">Update</a> contact/membership info.<font style="font-size:14px" face="Verdana" color="#000000">&nbsp;&nbsp;|&nbsp;&nbsp;<font style="font-size:12px" face="Verdana" color="#3c3c3c"><a href=https:///fe_web_member_subscription_verify.php?oeid= 
**[email protected]**&cid=9498

I tried to use online tools available on the internet but they are of no use / they extract only the first available email address in the excel field.

https://www.extendoffice.com/product/kutools-for-excel/excel-extract-email-address-from-string.html

There are n number of rows.

Is there a way to extract multiple email address from excel cell

Required output - extract emails and save them in a separate spread sheet or in the adjacent column

=========================

Input Format 3

For the following HTML code present in the excel cell I'm able to extract only the first occurence of the email that I get but not all the multiple email address mentioned.

            <p align="center"  style="margin-left:0px;margin-top:30px; margin-right:0px;margin-bottom:25px;">
                <table width="1024" cellspacing="0" cellpadding="0"  border="0" >
                        <tr>
                        <td colspan="2"  style="border-bottom: solid 1px #ff7373; " valign="bottom">
                            <p style="margin-left:0px;margin-right:0px;margin-top:0px; margin-bottom:0px;"> 
                                <font style="font-size:12px" color="#3C3C3C" face="Verdana" >
                                    <a href=https://house.com/powerhouse/fe_web_member_unsubscription.php?cid=4870&[email protected]&mbrid=cf7cc75d395cbf901f73967b1267db42 style="text-decoration: none; color: red;">Remove/unsubscribe</a><font style="font-size:14px" face="Verdana" color="#000000">&nbsp;&nbsp;|&nbsp;&nbsp;<font style="font-size:12px" face="Verdana" color="#3c3c3c"><a href=https://house.com/powerhouse/fe_web_member_update.php?cid=4870&[email protected]&mbrid=cf7cc75d395cbf901f73967b1267db42 style="text-decoration: none;  color: blue;">Update</a> contact/membership info.<font style="font-size:14px" face="Verdana" color="#000000">&nbsp;&nbsp;|&nbsp;&nbsp;<font style="font-size:12px" face="Verdana" color="#3c3c3c"><a href=https://house.com/powerhouse/fe_web_member_subscription_verify.php?oeid=mod.s@solutionsinc.com&cid=4870 style="text-decoration: none;  color: blue;">Subscribe</a> to receive requirements
                                </font> 
                            </p>
                            </td>
                    </tr>

                      <tr>
                        <td colspan="2"  valign="middle">
                            <p style="margin-left:0px;margin-right:0px;margin-top:0px; margin-bottom:0px;"> 
                                <font style="font-size:15px" color="#3C3C3C" face="Verdana" >
                                        <br><u>From</u>:<br>
                                       mod,<br>
                                       Solution<br>                                            
                                       [email protected]<br>
                                       <span style="background-color: #FFFF00"><b>Reply to:</b></span> &nbsp;&nbsp;[email protected]<br><br>

                                </font> 
                            </p>
                        </td>
                    </tr>  

                        <tr >
                            <td  width="800" valign="top"  style="border-top: solid 1px #333399; ">
                                <p align="justify" style="margin-left:0px;margin-right:0px;margin-top:0px; margin-bottom:0px;"> 
                                <font style="font-size:12px" color="#666666" face="Verdana">
                                     <a href="http://www.res.com/#!products/c5n6" style="text-decoration: none; color: red" title="Click to view more details">.</a>
                                </font>
                            </p>
                            </td>

                                <td    valign="top" style="border-top: solid 1px #333399; ">
                                    <p  align="right" style="margin-left:0px;margin-right:0px;margin-top:0px; margin-bottom:0px;">  
                                <font style="font-size:10px" color="#3C3C3C" face="Verdana">

                                </font>
                            </p>
                                </td>
                        </tr>
                </table><br>
            </p>

Ron Rosenfeld

This User Defined Function (UDF) will return an array of the existing email addresses in the referenced cell.

The individual addresses can then be retrieved in a variety of methods:

Use the INDEX function
Enter the function as an array across several cells
Use it in a macro to perform specialized actions that you may require.

The character of an email address is determined by the regex pattern sPat and described in detail below.

You put the UDF into a regular module

Option Explicit
Function ExtrEmail(S As String) As String()
    Dim sTemp() As String
    Dim RE As Object, MC As Object, M As Object
    Const sPat As String = "\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}\b" 'Regex for emails
    Dim I As Long
    
'Initialize Regex engine
Set RE = CreateObject("vbscript.regexp")
With RE
    .Pattern = sPat
    .Global = True
    .ignorecase = True
    If .test(S) = True Then
        Set MC = .Execute(S)
        ReDim sTemp(1 To MC.Count)
        I = 0
        For Each M In MC
            I = I + 1
            sTemp(I) = M
        Next M
    End If
End With
ExtrEmail = sTemp
End Function

One way of using it is shown in the screenshot below (using the INDEX function). The formula is entered in B1 and filled to the right and down. The IFERROR clause causes it to return a null string if there are fewer matches than cells filled.

Edit: Picture changed to show all three examples

Regex Pattern Explanation

Emailaddress

\b[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}\b

Options: Case insensitive; ^$ don’t match at line breaks

Created with RegexBuddy

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-07-9

Comments

0 comments

From Dev

Related Related

Article