How to extract multiple emails separated with semicolon and brackets
Category: [Excel] Demo Available
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 |
Obtained from the OzGrid Help Forum.
Solution provided by Carim.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBA to send email (outlook) with title of workbook |
| How to extract multiple emails separated with semicolon and brackets |
| How to use VBA to send email (outlook) with title of workbook |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.