Extracting a Name from excel

Question

I have to extract Customer names in a column that is a combination of name and email address. Examples of what would appear in this column could look like:

John Smith Johnsmith@me.com

Joe Bloggs theycallmejoe@myemail.com

Justin Credible JustinC@provider.com

I have found this cool VBA to extract the email address.

Function ExtractEmailAddress(s As String) As String
    Dim AtSignLocation As Long
    Dim i As Long
    Dim TempStr As String
    Const CharList As String = "[A-Za-z0-9._-]"

    'Get location of the @
    AtSignLocation = InStr(s, "@")
    If AtSignLocation = 0 Then
        ExtractEmailAddress = "" 'not found
    Else
        TempStr = ""
        'Get 1st half of email address
        For i = AtSignLocation - 1 To 1 Step -1
            If Mid(s, i, 1) Like CharList Then
                TempStr = Mid(s, i, 1) & TempStr
            Else
                Exit For
            End If
        Next i
        If TempStr = "" Then Exit Function
        'get 2nd half
        TempStr = TempStr & "@"
        For i = AtSignLocation + 1 To Len(s)
            If Mid(s, i, 1) Like CharList Then
                TempStr = TempStr & Mid(s, i, 1)
            Else
                Exit For
            End If
        Next i
    End If
    'Remove trailing period if it exists
    If Right(TempStr, 1) = "." Then TempStr = _
       Left(TempStr, Len(TempStr) - 1)
    ExtractEmailAddress = TempStr
End Function

But I need something similar to extract the name as well.

Can anyone assist?

Answer 1

Did you try this non-VBA solution called Flash Fill

If you have data with same pattern (as for example e-mail address where you want to extract First name) then:

• In next column write example of what you want to extract (i.e. John Smith in your example):
• Select that cell and press Ctrl+E
• You will get list that you can further check.

Answer 2

Formula only approach relies on this Super User answer regarding finding the last space in a cell. You then just use LEFT(position_of_last_space-1) to get everything to the left of the e-mail address.

=LEFT(A1,FIND("`",SUBSTITUTE(A1," ","`",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)

The use of the back-tick is to be a substitute for spaces. The assumption is there is no back-tick in either the name or the e-mail address.

Example:

Answer 3

Just make another small function to get name by eliminating email address. See below...

Use of function in worksheet.

Function GetName(refCell As String)
Dim tempName As String
    tempName = Trim(Left(refCell, Len(refCell) - Len(ExtractEmailAddress(refCell))))
    GetName = tempName
End Function

'----------------------------------------------------------

Function ExtractEmailAddress(s As String) As String
    Dim AtSignLocation As Long
    Dim i As Long
    Dim TempStr As String
    Const CharList As String = "[A-Za-z0-9._-]"

    'Get location of the @
    AtSignLocation = InStr(s, "@")
    If AtSignLocation = 0 Then
        ExtractEmailAddress = "" 'not found
    Else
        TempStr = ""
        'Get 1st half of email address
        For i = AtSignLocation - 1 To 1 Step -1
            If Mid(s, i, 1) Like CharList Then
                TempStr = Mid(s, i, 1) & TempStr
            Else
                Exit For
            End If
        Next i
        If TempStr = "" Then Exit Function
        'get 2nd half
        TempStr = TempStr & "@"
        For i = AtSignLocation + 1 To Len(s)
            If Mid(s, i, 1) Like CharList Then
                TempStr = TempStr & Mid(s, i, 1)
            Else
                Exit For
            End If
        Next i
    End If
    'Remove trailing period if it exists
    If Right(TempStr, 1) = "." Then TempStr = _
       Left(TempStr, Len(TempStr) - 1)
    ExtractEmailAddress = TempStr
End Function

You can also use built in function in place of GetName with ExtractEmailAddress like

=TRIM(LEFT(A1,LEN(A1)-LEN(ExtractEmailAddress(A1))))