How to Extract multiple emails separated with semicolon and brackets

How to extract multiple emails separated with semicolon and brackets

Requirement:

The user has a list of emails in the same cell, each one inside brackets, separated with semicolons with a space as follows;

[[email protected]] ; [[email protected]] ; [[email protected]] ; [[email protected]] ; ... list goes on.

The main goal is to obtain all emails through a column from B1 to B50; how can I also manage semicolon number changed when the user filled down (in the formula as shown above, substitute refers to 3rd semicolon, but the user wants it increased one by one so that all emails can be listed in order from B1 to B50.

Solution:

=MID($A$1,IFERROR(FIND(CHAR(1),SUBSTITUTE($A$1,"[",CHAR(1),ROW())),"")+1,IFERROR(FIND(CHAR(1),SUBSTITUTE($A$1,"]",CHAR(1),ROW())),"")-(IFERROR(FIND(CHAR(1),SUBSTITUTE($A$1,"[",CHAR(1),ROW())),"")+1))

=IFERROR(FIND(CHAR(1),SUBSTITUTE($A$1,"[",CHAR(1),ROW())),"")

=IFERROR(FIND(CHAR(1),SUBSTITUTE($A$1,"]",CHAR(1),ROW())),"")

To result in the following:

[[email protected]] ; [[email protected]] ; [[email protected]] ; [[email protected]] ;	[email protected]	1	24	Formula in Column C is locating [ and Formula in Column D is locating ]
	[email protected]	28	51	*Row() allows to increment instance*
	[email protected]	55	80	*Both Formulas are integrated*
	[email protected]	84	111	*into the Formula in Column B*