3

How can I remove the email address and the phone number from the D columns using formula ?

I tried =LEFT(D2,FIND("@",D2)) but it doesn't fully remove the email address.

I tried to match the columns to left to remove but that didn't work.

=LEFT(D2,LEN(B2))

=TRIM(MID(D2,LEN(B2)+2,1000))

enter image description here

1
  • I hope that's dummy data and not real PII
    – Chris H
    Sep 27, 2020 at 18:51

4 Answers 4

3

It appears that your data consists of a set of items that:

  • are separated by comma space
  • may have an email as the last item

If this is always the case, then consider the following User Defined Function:

Public Function noemail(s As String) As String
    Dim i As Long, L As Long

    If InStr(s, "@") = 0 Then
        noemail = s
        Exit Function
    End If
    
    L = Len(s)
    For i = L To 1 Step -1
        If Mid(s, i, 1) = "," Then
            noemail = Left(s, i - 1)
            Exit Function
        End If
    Next i
    
End Function

It checks the string for @. If the @ is present, it looks for the preceding comma and clips the string at the point:

enter image description here

User Defined Functions (UDFs) are very easy to install and use:

  1. ALT-F11 brings up the VBE window
  2. ALT-I ALT-M opens a fresh module
  3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it. If you are using a version of Excel later then 2003, you must save the file as .xlsm rather than .xlsx

To remove the UDF:

  1. bring up the VBE window as above
  2. clear the code out
  3. close the VBE window

To use the UDF from Excel:

=noemail(A1)

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

and

http://msdn.microsoft.com/en-us/library/ee814735(v=office.14).aspx

and for specifics on UDFs, see:

http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx

Macros must be enabled for this to work!

3

You can also use the following formula:

=LEFT(D2,(FIND((MATCH(TRUE,ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9}&", ",D2)),0)-1)&",",D2)))

Explanation

  • FIND({0,1,2,3,4,5,6,7,8,9}&", ",D2): finds if there is a number followed by a comma. This returns 10 values. Only 1 will be correct.
  • MATCH(TRUE,ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9}&", ",D2)),0): combines the previous formula with ISNUMBER and MATCH functions to obtain the index of the number in the array constant.
  • FIND((MATCH(TRUE,ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9}&", ",D2)),0)-1)&",",D2): uses the index from the array constant to return the position of the string.
  • LEFT(D2,(FIND((MATCH(TRUE,ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9}&", ",D2)),0)-1)&",",D2))): combines all the above to return everything except the email address

enter image description here

Note that if the second part of the string ends with a number followed by a comma, then this formula will not work.

3
  • I think OP want to do the opposite to this, remove the email and just have the remaining text
    – PeterH
    Sep 26, 2020 at 15:12
  • @PeterH You are 100% right. Will delete my answer. Sep 26, 2020 at 15:13
  • Great answer +1 from me !
    – PeterH
    Sep 26, 2020 at 16:44
2

This will return all parts that do not contain @

=TEXTJOIN(", ",TRUE,FILTERXML("<a><b>"&SUBSTITUTE(A1,",","</b><b>")&"</b></a>","//b[not(contains(.,'@'))]"))

enter image description here

0

As much as I like the UDF from Gary's Student, you may also achieve this with a formula:

=LEFT(D2,SEARCH(TRIM(RIGHT(SUBSTITUTE(TRIM(D2), " ", REPT(" ", 99)), 99)),D2,1))

Note. I copied part of this from another answer that has been removed. (will delete mine if it gets reposted)

2
  • 1
    If a column doesn't have an email address it removes the required data
    – bOI
    Sep 26, 2020 at 15:30
  • Don't delete your answer. I have (re)posted a formula that is different from what I posted before. Sep 26, 2020 at 16:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .