how to extract multiple email address in a single cell

[badgerms]

Hi All,

Anyone can advise how to solve the above.

I have multiple email address in a single cell. But in order to extract them out, I have to do manually one by one.

Is there a way out of this ?

Please advise, thanks

 

[jbeaucaire]

AH, that's what I feared. You've gone from a clean and controlled data style to a semi-chaotic one. Any number of email addresses in that cell now? For this I would revert to VBA functions to do the various things.

One function to pull all the First name, another to pull the individual email addresses, if that's a need, etc.

 

[RAKESH230583]

Hi - I only require the first name, So that i can show it in Outlook email body like.

Dear First Name 1 / First Name 2 / First Name 3 ... ,

 

[jbeaucaire]

Here is a custom function you can add to an empty module:

Option Explicit

Function FirstNames(ByVal Target As Range, Optional Delim As String)
Dim MyArr As Variant, i As Long
Dim MyVal As String

If Len(Delim) = 0 Then Delim = ";"
MyVal = Target.Cells(1).Value

If InStr(MyVal, Delim) = 0 Then
    FirstNames = Left(MyVal, InStr(MyVal, ".") - 1)
Else
    MyArr = Split(MyVal, Delim)
    For i = LBound(MyArr) To UBound(MyArr)
        If Len(FirstNames) = 0 Then
            FirstNames = "Dear " & WorksheetFunction.Proper(Left(MyArr(i), InStr(MyArr(i), ".") - 1))
        Else
            FirstNames = FirstNames & " / " & WorksheetFunction.Proper(Left(MyArr(i), InStr(MyArr(i), ".") - 1))
        End If
    Next i
    FirstNames = FirstNames & ","
End If

End Function

Then, back on your worksheet, use the new function starting in C2 like so:

=FirstNames(B2, ";")

 

[Peter_SSs]

If you have a recent version of Excel with the TEXTJOIN function then I think you can do this with a worksheet formula - see column C below. This is an array formula so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied down.

If you still would prefer a user-defined function, here is another one you could consider - see code & column D.

Function FNames(s As String) As String
  Dim itm As Variant
  
  For Each itm In Split(s, ";")
    FNames = FNames & " / " & Split(itm, ".")(0)
  Next itm
  FNames = "Dear " & Application.Proper(Mid(FNames, 4)) & ","
End Function

Excel Workbook
	B	C	D
1	Email To
2	Amit.kumar@asia.mkassociate.com;sejal.vyas@algeria.mkassociate.com;nitin.sharan@asia.mkassociate.com	Dear Amit / Sejal / Nitin,	Dear Amit / Sejal / Nitin,
3	Delly.Dsouza@asia.mkassociate.com;prinu.Patel@asia.mkassociate.com	Dear Delly / Prinu,	Dear Delly / Prinu,
4	Vikas.Kumar@asia.mkassociate.com;royse.ph@asia.mkassociate.com;Mike.Ros@japan.mkassociate.com;Tejal.Kumar@asia.mkassocia te.com	Dear Vikas / Royse / Mike / Tejal,	Dear Vikas / Royse / Mike / Tejal,
5	tim.Smith@ada.net.uk;ed.jones@ffa.org.cz	Dear Tim / Ed,	Dear Tim / Ed,
6	ken.lim@abc.com	Dear Ken,	Dear Ken,
First Names