Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-06-2013, 01:26 PM
dunndealpr dunndealpr is offline need to extract email addresses from excel files Windows 7 64bit need to extract email addresses from excel files Office 2007
Novice
need to extract email addresses from excel files
 
Join Date: Aug 2011
Posts: 8
dunndealpr is on a distinguished road
Default need to extract email addresses from excel files

Hey there. I have many Excel files which each have four columns of data (A, B, C, and D) and tens of thousands of rows. I need any email addresses present in column D extracted to column E. What's the best solution here? The text surrounding the emails is always different (ie the emails are not surrounded by <> or any other kind of constant characters that can simplify this).



I've tried DigDB but it seems to have a run-time error 6 and/or 1004 issue no matter what I do. DigDB also misses more than a few emails that I can see in plan view. I've tried a couple other programs but they save the emails in a separate document, and I need the emails saved in the original file alongside the data it was extracted from, so that's no help to me.

Any ideas? Thanks in advance.
Reply With Quote
  #2  
Old 06-07-2013, 07:57 AM
Catalin.B Catalin.B is offline need to extract email addresses from excel files Windows Vista need to extract email addresses from excel files Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

Try this code, it will check every word ; if the word contains "@", then that word is sent for email validation function; if it passes validation, then the email address is sent to the next column cell:
Code:
Option Explicit
Sub ExtractMail()
Dim Result As Boolean
Dim i As Integer, j As Integer
Dim LastRow As Long
Dim TextString As Variant
Dim Words As String
LastRow = ActiveSheet.Cells(1, "D").End(xlDown).Row
    For i = 1 To LastRow
         TextString = Split(ActiveSheet.Cells(i, "D"), " ")
            For j = 0 To UBound(TextString)
                Words = TextString(j)
                    If InStr(1, Words, "@") > 0 Then
                        Result = ValidareAdresaEmail(Words)
                            If Result Then
                                ActiveSheet.Cells(i, "E") = Words
                             End If
                     End If
             Next j
    Next i
    
End Sub
Function ValidareAdresaEmail(ByVal AdresaEmail As String) As Boolean
    Dim objRegEx As Object
    On Error GoTo Fin
    Set objRegEx = CreateObject("Vbscript.Regexp")
    With objRegEx
        .Pattern = "[a-z0-9!#$%&'*+/=?^_`{|}~-]+(?:\.[a-z0-9!" & _
                        "#$%&'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:" & _
                        "[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:" & _
                        "[a-z0-9-]*[a-z0-9])?"

        .IgnoreCase = True
        ValidareAdresaEmail = .Test(AdresaEmail)
    End With
Fin:
    Set objRegEx = Nothing
    If Err.Number <> 0 Then MsgBox "Error: " & _
        Err.Number & " " & Err.Description
End Function
Reply With Quote
  #3  
Old 06-07-2013, 08:21 AM
dunndealpr dunndealpr is offline need to extract email addresses from excel files Windows 7 64bit need to extract email addresses from excel files Office 2007
Novice
need to extract email addresses from excel files
 
Join Date: Aug 2011
Posts: 8
dunndealpr is on a distinguished road
Default

Hi Catalin! Thank you so much for your response.
Few stupid questions flying your way now. I'm decent with VBA in Word but for some reason I'm dead in the water with Excel.

What is the name of your script? For instance, someone in another forum gave me a script. The first line was 'function extractemail' so whenever I want to run the script I click on the cell to the right of the cell with the email in it and I type =extractemail. What do I type for yours? If it's ValidareAdresaEmail, I tried it and ended up with the word TRUE in the cell, nothing else.

My second question is one I haven't yet had answered in the other forum and I'm sure it's because they're too busy shaking their heads at how clueless I am. How do I get these scripts to run on multiple rows? For instance with the other script I was highlighting all cells in the column next to the cells I wanted emails extracted from (in this case, cells B1 to B22), then in the formula field entering =extractemail(A1:A22). But I would always end up with #VALUE!

Thanks again for all your assistance.
Reply With Quote
  #4  
Old 06-07-2013, 08:29 AM
Catalin.B Catalin.B is offline need to extract email addresses from excel files Windows Vista need to extract email addresses from excel files Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

You have to run the procedure Sub ExtractMail()
Press Alt+F8 then select ExtractMail then Run... You don't have to type anything in sheet cells...
Just read the lines there and you will find out that this procedure will check all cells from column D
The UDF is not intended for use in sheet, it is for that sub, extract mail...
The function can be used in sheet, but just for one adress, like: =ValidareAdresaEmail("george@yahoo.com"), or: =ValidareAdresaEmail(A1); it will return true if the email is valid, false if not...
For more than one cell, ExtractMail sub will analyze all words in all cells from column D.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
need to extract email addresses from excel files VBA code to extract specific bookmarks from multiple word files Rattykins Word VBA 4 06-27-2012 10:02 PM
need to extract email addresses from excel files how to extract wav files from ppts t-4-2 PowerPoint 2 01-19-2012 02:24 AM
attach multiple excel files to same email using send to option cbtac Excel 0 01-04-2012 07:20 AM
need to extract email addresses from excel files Extract email address from field zssteen Excel 1 06-19-2009 02:32 AM
How Many Email Addresses Can You email at one time in Outlook zinfandel72 Outlook 2 08-04-2008 06:39 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:51 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft