#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
VBA code to extract specific bookmarks from multiple word files | Rattykins | Word VBA | 4 | 06-27-2012 10:02 PM |
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 |
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 |