Arranging emails format in cell

[dessim]

I have this issue here. I was given different emails of personnel related to a project in a single cell.
As shown below:

Project A personnel

abc@gmail.com,efg@gmail.com, fgx@hotmail.com, 1234itoday@gmail.com

Is there anyway I could arrange the emails from a horizontal format to a vertical format within the same cell?

Project A personnel

abc@gmail.com efg@gmail.com fgx@hotmail.com 1234itoday@gmail.com

Seek some kind help here.
Please advise

Thank you

 

[JamesCanale]

=SUBSTITUTE(B1,",",CHAR(10))

Make sure you wrap text.

 

[dessim]

Thank you very much.
You sure save my day.

 

[Peter_SSs]

If you didn't want to use another column to do it you could do FIND/REPLACE a couple of times and manually set Wrap Text or you could run this short macro to do the steps.
Test with a copy of the data as it over-writes the original.
If your sample data is accurate you have some CHAR(160) characters that look like space characters but my code will remove those too.

Sub WrapEmails()
  With Range("B2", Range("B" & Rows.Count).End(xlUp))
    .Value = Evaluate("substitute(substitute(substitute(" & .Address & ",char(160),""""),"" "",""""),"","",char(10))")
  End With
End Sub

 

[dessim]

Evening to all,
What can I do If there is another scenario?

How could I arrange them into:

Mr Abc, Staff officer, Budget Dpt<Abc@buget.com>
Ms Def, Admin Officer, HR Dpt<Def@HR2.com>
Mr Ghr, Asst Mgr, Pd Dpt<Ghr@hotmail.com>

Project B

Mr Abc, Staff officer, Budget Dpt<Abc@buget.com>; Ms Def, Admin Officer, HR Dpt<Def@HR2.com>; Mr Ghr, Asst Mgr, Pd Dpt<Ghr@hotmail.com>

 

[JamesCanale]

surely:

=SUBSTITUTE(B1,"; ",CHAR(10))

and don't forget to wrap text

 

[Peter_SSs]

.. or again you could do it in the original cells with Find/Replace. In the Find what box enter ;
In the Replace with box, put your cursor and then type Ctrl+J
You won't see anything in that box but set the other settings as shown then click 'Replace All'

.. or again with macro including setting the wrap text and adjusting row height

Sub SplitEmails()
    With Columns("A")
      .Replace What:=";", Replacement:=Chr(10), LookAt:=xlPart
      .WrapText = True
      .Rows.AutoFit
    End With
End Sub