deleting part of a cell

**tolly** · 01-19-2010, 07:42 AM

Hello

I have a huge Excel database of 10000 email addresses, and in order to import them in to my email accounts address book, I need to remove the the persons name so I am just left with the email address.

ie;

(All in one cell)
John Smith <johnsmith@help.co.uk>

needs to become

<johnsmith@help.co.uk>

I am wondering if there is an action or something that I can run so I don't have to delete each name by hand more than 1000 times?

Hope someone can help.

Kind Regards

**Richard Buttrey** · 01-19-2010, 07:49 AM

Hi,

Use a helper column and use

=RIGHT(A1,LEN(A1)-FIND("<",A1)+1)

Copy this down your 1000+ rows then if necessary copy and paste special on top of the original data.

HTH

**royUK** · 01-19-2010, 08:03 AM

Here's a User Defined Function

'---------------------------------------------------------------------------------------
' Module    : Module1
' Author    : Roy Cox
' Date      : 26/07/2008
' Purpose   : Extract email address from string
'---------------------------------------------------------------------------------------

Option Explicit

Function GetMailAdd(rCl As Range) As String

    Dim sText As String
    Dim iStart As Integer
    Dim iEnd As Integer
    Dim iPos As Integer

    On Error GoTo GetMailAdd_Error

    sText = rCl.Text
    iPos = InStr(1, sText, "@")
    iStart = InStrRev(sText, " ", iPos)
    If iStart = 0 Then iStart = 1
    iEnd = InStr(iPos, sText, " ")
    If iEnd = 0 Then iEnd = Len(sText) + 1

    GetMailAdd = Trim(Mid(sText, iStart, iEnd - iStart))

    If Right(GetMailAdd, 1) = "." Then
        GetMailAdd Left(GetMailAdd, Len(GetMailAdd) - 1)
    Else
        GetMailAdd = GetMailAdd
    End If

    On Error GoTo 0
    Exit Function

GetMailAdd_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetMailAdd of Module Module1"
End Function

Use as an ordinary function

=getmailadd(A1)