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]

Absolutely. VBA or some formulas might do it, post up a good sample set of data.

 

[badgerms]

Hi jbeaucaire

here the data, appreciate your help, thanks


apus98@singnet.com.sg;aptreal@singnet.com.sg;aptkl@singnet.com.sg;aptech@singnet.com.sg;aptconst@singnet.com.sg;apt212@singnet.com.sg;apssin@singnet.com.sg;apspl@singnet.com.sg;apspi@singnet.com.sg;aprsi@singnet.com.sg;aprkeng@singnet.com.sg;apptech@singnet.com.sg;applica@singnet.com.sg;appleton@singnet.com.sg;appic@singnet.com.sg;appco@singnet.com.sg;apparels@singnet.com.sg;apower@singnet.com.sg;apossys@singnet.com.sg;apnprop@singnet.com.sg;apmstech@singnet.com.sg;apmmb@singnet.com.sg;aplusea@singnet.com.sg;aplsin@singnet.com.sg;aplsbs@singnet.com.sg;apljob@singnet.com.sg;aplio@singnet.com.sg;aplc01@singnet.com.sg;apimd@singnet.com.sg;apifa@singnet.com.sg;apiepl@singnet.com.sg;apexsin@singnet.com.sg;apexphar@singnet.com.sg

 

[jbeaucaire]

If the data is really that simple, then:

1) Make sure the data is all in one column and the columns to the right are empty
2) Highlight the column of data
3) Select Data > Text To Colums > Delimited > Other: ; > Finish

That should do it.

 

[jbeaucaire]

If all of those email addresses are completely inside of A1 and column B is empty, try this macro to put them all in column B for you:

Option Explicit

Sub ParseStrings()
Dim MyArr As Variant

MyArr = Split(Range("A1"), ";")
Range("B1").Resize(UBound(MyArr) + 1).Value = _
    Application.WorksheetFunction.Transpose(MyArr)

End Sub

 

[badgerms]

If the data is really that simple, then:

1) Make sure the data is all in one column and the columns to the right are empty
2) Highlight the column of data
3) Select Data > Text To Colums > Delimited > Other: ; > Finish

That should do it.

Hi jbeaucaire

Thanks, the above method works, thank you for your help !

 

[bizabcs]

ABSOLUTELY AWESOME Thanks a tom for info

If the data is really that simple, then:

1) Make sure the data is all in one column and the columns to the right are empty
2) Highlight the column of data
3) Select Data > Text To Colums > Delimited > Other: ; > Finish

That should do it.

 

[RAKESH230583]

With in Similar line of above Post -

Case Details :
I have a sub, which is used to auto generate email, based on information shared in userform.

Email details are maintained in "Source" Sheet, Column E and in Column F, i have given formula (=Proper(Iferror(Left(E2,Search(".",E2)-1),"")) to Extract first name from email address.

Email format : "FirstName.LastName@country.ABC.com" (There will not be any change in Email format)

Using below code in Sub, Which will then be used to dispaly (Dear FirstName,)
EmailTo = Worksheets("Source").Range("A:A").Find(What:=Userform1.Combobox1.value).Offset(0,5).Value

Problem: Till now, there was only 1 email address in single cell, butNow, due to some additional requirement, column E, single cell will have more than 1 Email addressees because of which i got stuck and not able to get the desire result in email body for E.g. Dear FirstName (1st Email) / FirstName (2nd Email) ,

Can anyone, please confirm whether its possible to get the desire results in email body.
If yes - Please share the way out to achieve it.

 

[jbeaucaire]

To do this using formulas only, we would need to see a very clear and accurate example of all the variations that would be seen in the cells in question. Show us some good examples and we'll see what we can come up with.

Of course you resorting to VBA, Anything is Possible. But it's probably possible with formulas to. Show us the before and the after and we'll see if we can connect the dots.

 

[RAKESH230583]

Hi Thanks for your revert - Please find below details as requested.

Sheet Name : Source

	A	B	C
1	Country List	Email To
2	Country A	Amit.kumar@asia.mkassociate.com;Sejal.vyas@algeria.mkassociate.com;nitin.sharan@asia.mkassociate.com	=Proper(Iferror(Left(B2,Search(".",B2)-1,""))
3	Country B	Delly.Dsouza@asia.mkassociate.com;prinu.Patel@asia.mkassociate.com	=Proper(Iferror(Left(B3,Search(".",B3)-1,""))
4	Country C	Vikas.Kumar@asia.mkassociate.com;royse.ph@asia.mkassociate.com;Mike.Ros@japan.mkassociate.com;Tejal.Kumar@asia.mkassociate.com	=Proper(Iferror(Left(B4 ,Search(".",B4)-1,""))​

<tbody>
</tbody>

Initially, when there was requirement of only 1 email address - Which the help of given formula in Row C, I was able to get the First Name.

Which gets captured in email by using the below codes.
EmailTo = Worksheets("Source").Range("A:A").Find(What:=Userform1.Combobox1.value).Offset(0,2).Value

Which then i use in - Olemail = Olapp.createitem(olmailItem) in email body.

For Country A :
Earlier result : Dear Amit, (If single email address is maintained in B2)

Current requirement : Dear Amit / Sejal / Nitin, (Where more than 1 Email address are maintained in B2)