Split Text

[Kenshin]

Maybe thihs a simple question but make me a headache, how we split the text like this

The data is always like this:
Andrew, M.Sc(1); Anton, Ph.D(2); Anita, MA(3)

and the results are:

Andrew, M.Sc.
Anton, Ph.D.
Anita, MA

Hope my explanation is clear for you guys,

 

[N Pande]

You can try Data > Text to Columns and delimiter. Use ; as delimiter.
The suffix (1), (2),(3) are they part of the text too? Do they need to be removed or you have included to help clarify the problem.

 

[GraH - Guido]

If possible consider using Power Query (Get & Transform).

Load from table
Transform - Split Column and define this.

Repeat the same to split in columns at first occurrence of "("
Then save and load to table

 

[Devender Kumar Yadav]

try Data > Text to Columns and delimiter. Use ; as delimiter. after break data in column copy and paste special transpose.