separate out email address

[dennisli]

Good evening, I have a combined sentence with email address at the end. There is a space between email and other part.
For example, in Cell A1, it's: PO Box 132, Washington Ave, dennis789@yahoo.com.
In cell A2, it changes to: 12 DW Road, georgeyiui@hotmail.com.
How can I separete these emails out?
thanks lot.

 

[pbornemeier]

This code will read the email address from the last part of the cell in column A and place it in column B:

Option Explicit 

Sub ExtractEmailAddressFromColumnAToColumnB()
    
    Dim lLastA1Row As Long
    Dim lX As Long
    
    lLastA1Row = Cells(Rows.Count, 1).End(xlUp).Row
    For lX = 1 To lLastA1Row
        If InStr(Cells(lX, 1).Value, "@") > 0 Then
            Cells(lX, 2).Value = Mid(Cells(lX, 1).Value, InStrRev(Cells(lX, 1).Value, " ") + 1, 1000)
            If Right(Cells(lX, 2).Value, 1) = "." Then
                 Cells(lX, 2).Value = Left(Cells(lX, 2).Value, Len(Cells(lX, 2).Value) - 1)
            End If
        End If
    Next

End Sub

 

[BenMiller]

Is there anything in the cell after the email address? If not, you can try typing this in B1 - no VBA required.

=RIGHT(A1,LEN(A1)-FIND("?",SUBSTITUTE(A1," ","?",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

It might be wrong, and there's definitely a shorter way to do it, but I'm not at a computer now... Might have screwed up the parentheses too.

 

[Rick Rothstein]

This formula will retrieve the text after the last space character in a text string (whether it is an email address or not)...

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

The 99 assumes the text being searched always less than 100 characters long. If the text could be bigger, then change the two 99's to a number bigger than that maximum length.

 

[BenMiller]

This formula will retrieve the text after the last space character in a text string (whether it is an email address or not)...

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),99))

The 99 assumes the text being searched always less than 100 characters long. If the text could be bigger, then change the two 99's to a number bigger than that maximum length.

Ahh, thanks Rick! I always manage to make things too complicated
I once made a table of the eight most common text-extracting formulas: First word, first two (or more) words, everything but first word, everything but first two (or more) words, and the same four; but for last instead of first. If I dig it up sometime maybe I'll post it
It should solve about 5% of all posts on this forum!

 

[Kyle123]

Another VBA way without a loop:

Sub ExtractEmail()    
With Columns(2).Cells(1).Resize(Columns(1).Cells(1).End(xlDown).Row, 1)
        .Value = Application.Transpose(Filter(Split(Join(Application.Transpose(.Offset(, -1)), " "), " "), "@"))
    End With
End Sub

 

[Rick Rothstein]

Ahh, thanks Rick! I always manage to make things too complicated
I once made a table of the eight most common text-extracting formulas: First word, first two (or more) words, everything but first word, everything but first two (or more) words, and the same four; but for last instead of first. If I dig it up sometime maybe I'll post it
It should solve about 5% of all posts on this forum!

I liked the idea of finding the first and last so many words from a text string, so I created a mini-blog article showing the formulas I came up with. You can find that article at this link...

Find the First and Last So Many Words in a Text String

 

[dennisli]

Thanks lot. Have a good day.