Comments on Yogesh Gupta's Excel Tips: Excel Macro : Email Address Extract from String

Wonderful. Thanks a lot

2012-02-09T14:52:44.918+05:30

Wonderful. Thanks a lot

I have a list of company websites that i would lik...

2011-04-18T19:27:42.907+05:30

I have a list of company websites that i would like to extract the email addresses. Anyone would be able to explain how this is done in Excel? Or of this is even possible?

2011-03-28T02:24:26.782+05:30
Nice thread! Thanks

Hi Niku I do not have solution for this. Regards...

2010-08-07T17:35:43.321+05:30

Hi Niku

I do not have solution for this.

Regards

Thanks for your reply I m clearing my question I...

2010-08-06T12:16:53.617+05:30

Thanks for your reply

I m clearing my question I have a file in which details of customers available eg.cust name mobile number etc.. one file for each customer and i have to make MIS of that pdf files we have generated online in one excel file.
so any solution for that looking for early reply

Hi Niku I am not clear about your question. If ...

2010-08-04T19:49:45.567+05:30

Hi Niku

I am not clear about your question.

If you want to read and update the content of the PDF files into Excel Sheet. Then I do not have solution for you.

If you just want the list of the files to be updated then it can be done with the help of VBA.

Pls revert back.

Regards

Hi Yogesh, Your blog is awsome to excel learners....

2010-08-04T17:45:15.049+05:30

Hi Yogesh,

Your blog is awsome to excel learners.
I have 300+ pdf files which i have to load into one excel sheet about its contents can u show me a formula or a vb code to create one excel file having details of all pdf files..

Is there any way to do that
Hope you understand its urgency!!!

Though I had included credits to you in the downlo...

2009-11-19T14:43:38.819+05:30

Though I had included credits to you in the download file but not mentined about the differnece between two.

Now I have mentioned the difference and updated the file at download link.

Now some thing beyond this post. I want to invite you for guest post on my blog. Any topic or any tips of your choice. Let me know if you are okay with this. I will be honored if you accept my invite.

Good catch! That is what I get for making last min...

2009-11-19T14:20:25.034+05:30

Good catch! That is what I get for making last minute changes and not testing them. I'm glad spotted the problem and were able to figure out the solution. Thanks.

Just a thought... as long as you decided to include my code in the download file, you might want to include a comment as to what the difference is between your code and mine, just in case someone reads your blog entry and downloads the file directly without reading the comments.

Rick Rothstein (MVP - Excel)

Hi Rick Thanks for your inputs. I tested the code...

2009-11-19T11:14:23.844+05:30

Hi Rick

Thanks for your inputs. I tested the code and feel that there is some gap. After the first for next blok, "AtSign" variable needs to be recalculated by using statement
AtSign = InStr(S, "@")

It works fine after this change.

I have updated the file at download link in the original post to include your code after this correction.

Regards//Yogesh Gupta

Here is a macro that is a little more robust than ...

2009-11-19T02:06:10.068+05:30

Here is a macro that is a little more robust than the one you posted... instead of using just a blank space to delimit the email address from the rest of the text, it uses any character that is invalid in an email address to delimit the email address from the rest of the text. That way, you will be able to retrieve the email address if it is, for example, surrounded by parentheses, quote marks, etc. You will also be able to find the email address if it located at the end of a sentence (email addresses cannot start or end with a dot). Here is the code...

Function GetEmailAddress(ByVal S As String) As String
Dim X As Long, AtSign As Long
Dim Locale As String, DomainPart As String
Locale = "[A-Za-z0-9.!#$%&'*/=?^_`{|}~+-]"
Domain = "[A-Za-z0-9._-]"
AtSign = InStr(S, "@")
For X = AtSign To 1 Step -1
If Not Mid(" " & S, X, 1) Like Locale Then
S = Mid(S, X)
If Left(S, 1) = "." Then S = Mid(S, 2)
Exit For
End If
Next
For X = AtSign + 1 To Len(S) + 1
If Not Mid(S & " ", X, 1) Like Domain Then
S = Left(S, X - 1)
If Right(S, 1) = "." Then S = Left(S, Len(S) - 1)
GetEmailAddress = S
Exit For
End If
Next
End Function

Also note that I changed the argument's data type from Range to String... doing this still allows you to pass a cell reference into it when using it as a UDF, but it also allows you to pass it a simple text string as well (useful if you want to use the function in a non-UDF setting).

Rick Rothstein (MVP - Excel)

Hello Rick I have not made any change in the code...

2009-11-18T23:09:13.866+05:30

Hello Rick

I have not made any change in the code display. Could not understand why is it behaving like this. You can download the excel file having the code.

Thanks for your formula input.

Regards//Yogesh Gupta

I just noticed that my formula has four blank ...

2009-11-18T21:00:38.442+05:30

I just noticed that my formula has **four** blank spaces in it, not three and it looks like your display word wrapped it at the space I forgot to count. However, that should not matter since when I copy the formula from your webpage, it pastes correctly into Excel's Formula Bar as a single continuous line.

Here is a worksheet formula that will extract an e...

2009-11-18T20:34:44.178+05:30

Here is a worksheet formula that will extract an email address from the text in a cell...

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND("@",A1)-1)," ",REPT(" ",99)),99))&MID(A1,FIND("@",A1),FIND(" ",A1&" ",FIND("@",A1))-FIND("@",A1))

In case your code displayer ends up breaking the line at blank spaces, I just want to point out that there are three blank spaces surrounded by quote marks in the formula... one on each side of the REPT function and one after the third FIND function.

Rick Rothstein (MVP - Excel)

Did you change your code displayer? I don't re...

2009-11-18T20:28:51.814+05:30

Did you change your code displayer? I don't remember this happening before, but when I copy/pasted your code into my copy of XL2003, everything was on a single line (that is, there were no line feeds between the individual statements).

Comments on Yogesh Gupta's Excel Tips: Excel Macro : Email Address Extract from String

Wonderful. Thanks a lot

I have a list of company websites that i would lik...

2011-03-28T02:24:26.782+05:30 Nice thread! Thanks

Hi Niku I do not have solution for this. Regards...

Thanks for your reply I m clearing my question I...

Hi Niku I am not clear about your question. If ...

Hi Yogesh, Your blog is awsome to excel learners....

Though I had included credits to you in the downlo...

Good catch! That is what I get for making last min...

Hi Rick Thanks for your inputs. I tested the code...

Here is a macro that is a little more robust than ...

Hello Rick I have not made any change in the code...

I just noticed that my formula has **four** blank ...

Here is a worksheet formula that will extract an e...

Did you change your code displayer? I don't re...

2011-03-28T02:24:26.782+05:30
Nice thread! Thanks

I just noticed that my formula has four blank ...