Free Microsoft Excel 2013 Quick Reference

Extract a domain name from an email address

I have a massive list of email addresses, and I need a formula to easily extract the domain name from each.

For example, in cell A1 I have the email address: fred.claus@northpole.com

In cell B1, I want to extract just the domain name: @northpole.com

I found the following formula, but I keep getting an error when I try to use it: =RIGHT(A1,((LEN(A1)-FIND(“@”,A1))))

Attached is a sample excel file.


I need some help in cleaning up a database of email address'. I have a
spreadsheet from a supplier that has 5164 records. Each line contains an
email address- contact First name and contact last name - company name.

The problem is that some of the records contain a company name and some do
not. I can get the company name from the email address but I am hoping not to
have to look through each address.

Does anyone know of a formula that I could use to strip off the characters
before the @ sign of the email address. I could sort by what remains and be
able to complete the spreadsheet a lot easier. I thought of using the left
formula but the problem is that names are different characters in length so
that won't work.

Any help would be apprciated.

Hi everyone,

I am pretty new at this and I was wondering if you could help me out.

I created a VB form that allows the user to input their email account
which will be used to send them a 'verification' code. The
verification code is created using a random number macro. Is there any
way to email a single worksheet to an email address? Another option
that might work a little better is to email the text from one cell to
the email address. Any idea how to do this?!

Here is what I have for coding so far (please don't laugh):

Private Sub CommandButton3_Click()
Randomize
MyNumber = Int((1000000 - 1 + 1) * Rnd + 1)
Sheet7.Range("f1").Value = "R" & MyNumber
Sheet8.Range("a1").Value = "Your Verification Code is: " &
Sheet7.Range("f1").Value
Sheet8.Range("a2").Value = ""
''''''''''
ActiveWorkbook.SendMail Recipients:=sheet7.Range("d1").value
''''''''''
'Sheet8.SendMail Recipients:=Sheet7.Range("d1").Value (could I use
this line as a
'subst.for the line immediately above?
End Sub

Hopefully I am not too far off. Also, would I have to send this using
Outlook?

Thank you for your help!

Hi,

I have found a formula to separate an email address but would like it to
leave the @

=RIGHT(A1,LEN(A1)-SEARCH("@",A1))

For example, this formula takes no@no.com and returns no.com

Is there a way to make it return @no.com

Thanks,
Bert

Is there a way to insert an email address in Excel so when someone clicks it will activate their email programn?

Hi,

1) How do i extract the part AFTER the '@' from an email address e.g. if the email address in A1 is abcdef@hotmail.com then in B1 i only want 'hotmail.com' to be returned. I'm looking for something generic that will work with any email address

2) Kind of related to the above. Suppose I have 1000 email addresses in column A

a) In column B (against each email address), I want to list the total number of times the same exact email address appears in the list

b) In column C I will have the result of the solution from my first question (above)

c) In column D, I would like to have the want the same count logic for the domain part (as I am requesting for the occurences of email addresses).

Hope my post is clear. Will appreciate your help.

I have a vendor list of over 3,000 email addresses. Some of the cells contain multiple email addresses that look like this:

John Doe ; Jane Doe jane.doe@domain.com;

I want to know if there is a macro/vbe code or formula that could separate the full names from the email address and span it over three columns like this:

Column 1 Column 2 Column 3
Doe, John Doe, J john.doe@domain.com

To make matters worse, some of vendors may have four different email addresses such as j.doe@domain.com; johndoe@domain.com; doejohn@doman.com, etc.

I would appreciate any suggestions. Thanks

Hello All,

I need assistance with what appears to be a simple task, but I am unable to accomplish.

I need to take contents of cells (which are results of formulas and references to other cells) and use the contents to compose an email.

Here are some cells to illustrate. NOTE: the information in brackets is the formula that's contained in the cell, while the text is the result of the formula.

B3 = This is the link used to compose an email.
C19 = abc@domain.com (an email address)
C22 = Login expired on node citrix08. [=concatenate("Login expired on node ",C7,".")]
C25 = Dear Company A, [=concatenate("Dear ",C6,",")]
C27 = The login for citrix08 has expired. [=concatenate("The login for ",C7," has expired.")
C29 = Contact us within 15 days to continue. [=concatenate("Contact us within ",C9," days ...]
C31 = Ticket 444 has been created to track the issue. [=concatenate("Ticket ",C10," has...]
C33 = Lots of other text
D33 = More text
E33 = More text

C48 = Sincerely,

B3 (Named "SEND MAIL") contains the formula used to compose the email from the cells above, however, I cannot add all the cells to the formula without an error. At some point, there is too much data (contained in the referenced cells) and B3 returns #VALUE error.

=HYPERLINK("mailto:" & C19 &"?subject=" & C22 & "&body=" & C25 & "%0D%0A" & "%0D%0A" & C27 & "%0D%0A" & "%0D%0A" & "%0D%0A", "SEND MAIL")

The above formula works now, because it doesn't contain all of the body (of email) I need.

When I click on B3, it properly launches the email client, puts in an address, subject, and two lines of text in the body.

My workaround to this problem is to manually highlight cells C29 through E48, select copy, then go to the partially formed email and paste the rest of the body.

Is there a way to include all the body into an email? I think this can be accomplished with a VBA code, but have no idea how to do it.

I dont want to send the mail, just compose it using cells that have calculated what is needed in the body. I can then read the email, confirm it's content, and click send in the email client manually.

Does anyone have any suggestions? Is there a better (but simple) approach?

I know this could be done via javascript on an html page, but I dont know how to code this at all.
I do know excell quite well and have many cells to calculate what is needed in the body of the email, but, alas,
still can't compose the ENTIRE email from the formula in B3.

Thank you in advance for your help.

Sincerely,
Robert

I have a document where everything in column B is a person's name. I want to convert that to an email address, whether by replacing the contents of the cells with the email address or pasting the info into a blank column C, doesn't matter. The name is always laid out as "Smith,Fred A" or just "Smith,Fred" (no spaces). I want to convert it to something like "fred.smith@youremail.com".

Secondly, and I imagine this one will be easier, in a column next to the converted email address, the first time that email address is listed, I would like it to say "yes", and every subsequent time for it to say "no".

You guys rock!

Todd

Hi Guys,

I want to extract the class C address from an IP address via a formula in Excel. For example the IP address 10.10.10.10 should return a class C address 10.10.10.0 and the IP address 10.171.55.253 should return a class C address 10.171.55.0. Is this possible ?

Floep

Please help with an excel formula to generate a login username (with all characters in lower case) from the beginning of an email address. For example,

For Email: Nosipho.dhladhla@un.org, formula should generate login nosipho.dhladhla

I was wondering if this is possible.

I have a column of data with peoples names e.g. Joe Blogs
the text Joe Blogs being in one cell .
How do I:

Grab that string
find the space and insert a . (so - joe.blogs)
then add the rest of an email address e.g. @hotmail.com

Basically I have created code that automatically inserts the workbook into an email attachment. I want to be able to insert an email address based on the name selected and create the email address.

Hi,

I got a list of customers emails and would like to extract their domain names frm the email list.

If I'm gonna used the RIGHT function, the result wouldn't be correct since different domains have diff chars length.

Example as follows:-

01@yahoo.com
ka@ehotelier.com
37@gmail.com
y@yahoo.com.my
h@streamyx.com

==> How do I extract the domain name after the @.

Your help is highly appreciated. tq.

Hello

I want to make a survey about which domain name is the most used. So I have
a list of email addresses in Excel. But I want just the domain names of the
email addresses. Then I will make a PivotTable so I can see every domain name
and their amount! Now my problem is, how can I get the domain names out of
the email addresses? Is it possible there is any function in Excel? Can
someone help me please?

I have a work project in which I am required to produce a list of peoples names with the accompanying email addresses and phone numbers. Sounds easy enough except for the fact the person who designed our Corporate database did so in an awful way so rather than be able to do so in Business Objects, I have to pull a list of names with email addresses first then seperately pull one with names and phone numbers. As part of this project, a list that has name, email and phone number in one row is required.

Is there a formula where I can match based on the first/last name of a person then bring over their email address/phone number from the next sheet? In the sample I have one sheet with the names and phone numbers then another with the names and addresses. How do I produce a single row that has both? I seem stuck as my Excel skills are lacking in this regard.

As a note there may not always be a match as in some cases someone with a phone number may not have an email address or vice versa, which is fine. In these cases where they do I need to show that though. I hope I was clear enough...

When I Googled for ways to validate email addresses, little did I imagine the long and windy road I was heading down. I found what appeared to be some very nifty (and concise/efficient) methods employing something called "Regex", but was never able to identify a library reference in VBA that made them usable. Since my users wanted an Excel spreadsheet user form, I needed a VBA solution.

So, I bit the bullet and wrote my own, based on the syntax rules I found in Wikipedia under entries "Domain Name - Syntax" and "Email Address - Syntax". At the risk of much derisive laughter for how long and compulsive it is, I thought I would post it here for anyone who doesn't have the hours to flush down the toilet, and needs something that will work. BIG CAVEAT: This does not test that the address actually exists as a deliverable destination - only that its form complies with commonly accepted syntax requirements.

The basic concept is to set the function's return to False at the outset. Then apply a whole bunch of nitpicky tests to the various address bits, for each way of constructing a valid address. At any point a test is not passed, the function is exited and the returned value is False. If you get to the end having passed all the tests, the function returns True. So with no further ado, here is the code. I hope it does someone some good.


	VB:
	
 
     
    Dim myStr As String 
    Dim MyChar As String 
    Dim i As Long 
    Dim n As Long 
    Dim myArr 
     
    emailTest = False 
     
     'test for 1 and only 1 "@" character, and no double 'dots'.
    If InStr(1, eAddr, "..") > 0 Then Exit Function 
    myArr = Split(eAddr, "@") 
    If UBound(myArr)  1 Then Exit Function 
     
     'tests for local segment of address (left of @)
    myStr = myArr(0) 
     'Local name segment can be entirely in quotes (chr34) with any characters in between.
    If Left(myStr, 1) = Chr(34) And Right(myStr, 1) = Chr(34) _ 
    And Len(myStr) > 3 Then 
         'skip next step - evaluation by character
    Else 
        For i = 1 To Len(myStr) 
            MyChar = Mid(myStr, i, 1) 
            Select Case MyChar 
            Case "0" To "9", "a" To "z", "A" To "Z" 
                 'allow, and continue to next char
            Case "-", "!", "#", "$", "%", "'", "*", _ 
                "+", "=", "?", "^", "`", "{", "}", _ 
                "|", "~", ".", "_" 
                 'these characters are not allowed at begin or end of local name
                If i = 1 Or i = Len(myStr) Then Exit Function 
            Case Else 
                Exit Function 
            End Select 
        Next i 
    End If 
     
     'tests for domain portion of address (right of @)
    myStr = myArr(1) 
    myArr = Split(myStr, ".") 
     'test for at least one dot
    If UBound(myArr) = 0 Then Exit Function 
     
     'test for domain as ip address
     '(i.e. must begin and end with [] brackets, and have four numeric segments
     ' separated by dots, each segment being one to three digits.
    If Left(myStr, 1) = "[" And Right(myStr, 1) = "]" _ 
    And UBound(myArr) = 3 Then 
        For n = 0 To 3 
            myStr = myArr(n) 
            myStr = Replace(myStr, "[", "") 
            myStr = Replace(myStr, "]", "") 
            If Len(myStr) > 3 Or Not IsNumeric(myStr) Then Exit Function 
        Next n 
        emailTest = True 
         'full address has passed, no need to continue evaluating domain chars
        Exit Function 
    End If 
     'continue with evaluating the domain by non-IP rules and by character compliance
    If Len(myStr) > 253 Then Exit Function 
    If UBound(myArr) > 126 Then Exit Function 
    For n = 0 To UBound(myArr) 
        myStr = myArr(n) 
         'last label (highest domain level) must be at least 2 characters
        If n = UBound(myArr) And Len(myStr) < 2 Then Exit Function 
        For i = 1 To Len(myStr) 
            MyChar = Mid(myStr, i, 1) 
            Select Case MyChar 
            Case 0 To 9, "a" To "z", "A" To "Z" 
            Case "-" 
                 'hyphen char may be in middle but not begining or end of label
                If i = 1 Or i = Len(myStr) Then Exit Function 
            Case Else 
                Exit Function 
            End Select 
        Next i 
    Next n 
     
    emailTest = True 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Dear Ozgrid community,

I got a slight problem with extracting a list of names from an existing table of names and certain criterias.
The new list would be shorter than the old list, basically I would like to filter out the Names of the guys that aren't IT-Technicians, I don't want to use the filter method though since the original table should stay in it's original form,
also VBA and macros, etc won't be of help as I need to get this working on google docs aswell.
I do admit I'm relatively new to excel and I know only basic commands, so I tried with an IF function

	VB:
	
(E2="IT-Tech",A2,"") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
it does what I need, it returns the value from A2 (which is the name) if the corresponding value in column E equals IT-Tech.
The main problem I have is that I'd like to be able to just drag down the list without having blank spots, because for every row where the guy is not an IT-Tech it creates a blank space.
Also the main list will be filled with new names and old ones removed from time to time, so it would be great if the new list could always get the data from the main table directly.

I do apologize for my English as I am not a native speaker.

Help with this matter would be greatly appreciated, I'm pretty sure that there is a logical solution for this, but i tried to find something for the past 6 hours and I'm close to giving up.

I also attached a file that might be easier to understand that what I wrote above.

Thanks in advance,

godded

I am using Xcell 2003. When I enter an email address within a cell it
automatically sets it up so that when you click on the cell it automatically
assumes you want to send that email address a message and outlook opens up.
Is there a way to turn this feature off as I want to display a list of email
addresses for a group of people on this spreadsheet however I don't want the
viewer to get discouraged everytime they click on the email address and
having outlook open. If that user doesn't want to email that person, they
then have to close outlook to get back to the spreadsheet and hope they use
the arrow keys in lieu of clicking on the cell.

Copy an email address
File, Send, As Attachment
Paste email address into Outlook TO: field.

The paste (and copy and cut) are grayed out. How can I do this.

We have been using a Word doc, and have just changed to an Excel form.

Hello!
I hope someone can help me.
I have following macro, that sends an email from Word document. It places prederermined text, subject line and email address into email. The only issue I'm having, is that I'm unable to tell macro to grab a pdf document from the specific location(web-site) and add it as an attachment to my document.

Here's part of the macro I'm working on right now

	VB:
	
 email 
    Dim oOutlookApp As Outlook.Application 
    Dim oItem As Outlook.MailItem 
    On Error Resume Next 
     'Start Outlook if it isn't running
    Set oOutlookApp = GetObject(, "Outlook.Application") 
    If Err  0 Then 
        Set oOutlookApp = CreateObject("Outlook.Application") 
    End If 
     'Create a new message
    Set oItem = oOutlookApp.CreateItem(olMailItem) 
     
     'Copy the open document
    Selection.WholeStory 
    Selection.Copy 
    Selection.End = True 
     'Set the WordEditor
    Dim objInsp As Outlook.Inspector 
    Dim wdEditor As Word.Document 
    Set objInsp = oItem.GetInspector 
    Set wdEditor = objInsp.WordEditor 
    Dim objRecip As Outlook.recipient 
     
     'Write the intro if specified
    Dim i As Integer 
    Dim myRange, strMsg, res As Variable 
     'Write the intro above the signature
    Set myRange = ActiveDocument.Range 
    wdEditor.Characters(1).Paste 
    i = wdEditor.Characters.Count 
    wdEditor.Characters(i + 1).InsertParagraph 
    i = i + 2 
     'Pop up box asking for the merchant's email address
    Dim recipient As String 
    recipient = InputBox("Please enter an email address") 
    If recipient = Empty Then 
        recipient = InputBox("This field cannot be empty! Please enter the merchant's email address:") 
        oCC.Range.Text = recipient 
        If recipient = Empty Then 
            msgbox1 = msgbox("This field cannot be empty! Please reopen the document and try again") 
            ActiveDocument.ActiveWindow.Close SaveChanges:=wdDoNotSaveChanges 
            End 
        End If 
    End If 
    Set objRecip = oItem.Recipients.Add(recipient) 
    objRecip.Type = olTo 
     'Display the email and send it
    Set objRecip = oItem.Recipients.Add(recipient) 
    objRecip.Type = olBCC 
    oItem.Display 
     
     
    If Not objRecip.Resolve Then 
        strMsg = "Could not resolve the Bcc recipient. " & _ 
        "Do you want still to send the message?" 
        res = msgbox(strMsg, vbYesNo + vbDefaultButton1, _ 
        "Could Not Resolve Bcc Recipient") 
        If res = vbNo Then 
            Exit Sub 
        End If 
    End If 
     
    oItem.Subject = "Subject line here" 
     'The body of this message will be
     'the content of the active document.
     
     'Clean up
    Set oItem = Nothing 
    Set oOutlookApp = Nothing 
    Set objInsp = Nothing 
    Set wdEditor = Nothing 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


If I have a list in excel (one column first names, next column last names) and they all have the same email pattern (first name, dot, last name@puppies.com, for example). Is there a way for excel to create in a third column a list of all those email addresses? Thanks a bunch

When I open a file (template) in Excel 2007 from an http address an "Opening..." message is briefly displayed

Workbooks.Open "http://server/folder/filename.xltm"

On other pc's connecting to other servers I do not see this "Opening.." message.
Opening the same file from within Excel File |Open etc does not display this message
No message is displayed using the same code in 2003 on the same pc from the same server.
Does anyone know if this is an Excel or pc setting that I can turn off?
Thanks

Is there an easy formula to tell you if an email address is formatted
correctly, with text@text.text? I don't even need to know more then that
(such as valid domains, etc.) for now.

My original thought was just a basic IF statement to drop in to the next
column that tells me if the cell is formatted correctly. But I don't know
how to look for text in a particular sequence or format.

Any ideas for something like this, or better? Thanks!
(You all always have the best answers!)

-bethp

What is the simpleest method to send and receive text strings to/from an IP address and port (ex.- XX.XXX.XX.XX:2000)? The objective is to to inquire and recieve readings from a laboratory device. I do not want to use forms to imbed controls as I want to make the code as portable as possible. The IP address and port are fixed and the "command" strings are three and four character strings preceeded by an asterisk. An example of pseudo code:

Connect to XX.XXX.XX.XX:2000
Send "*SRTF"
Temp=Retruned_String
range("A1").value=Temp

I would like to know what proper code to use if I want a textbox validate if the entry typed in is an email address and has no blank spaces in it. The textbox is in a worksheet and is in an activex form.

Thank you in advance!